Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-23-2021, 05:36 PM
Marcia's Avatar
Marcia Marcia is offline Use the > as text in formula Windows 10 Use the > as text in formula Office 2019
Expert
Use the > as text in formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Use the > as text in formula

Hi. Column A of a table has:
Travel
>aaa
>bbb
The values with the greater than symbol represent sub accounts. A problem came up when there is a SUMIF formula with column A as reference. It is reading the ">aaa" as greater than aaa.
How do I make the formula to read it as a plain character?


Thank you.
Reply With Quote
  #2  
Old 10-23-2021, 11:51 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Use the > as text in formula Windows 7 64bit Use the > as text in formula Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Perhaps inserting a tlde would help?
"~>"&"aaa"
Reply With Quote
  #3  
Old 10-24-2021, 01:55 AM
Marcia's Avatar
Marcia Marcia is offline Use the > as text in formula Windows 10 Use the > as text in formula Office 2019
Expert
Use the > as text in formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Pecoflyer View Post
Perhaps inserting a tlde would help?
"~>"&"aaa"
I tried that tilde but the formula is still adding the values of all accounts with > in column A.
Reply With Quote
  #4  
Old 10-24-2021, 03:00 AM
p45cal's Avatar
p45cal p45cal is offline Use the > as text in formula Windows 10 Use the > as text in formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

you could try the likes of:
=SUMIF($A$2:$A$5,"*aaa",$B$2:$B$5)
but the asterisk means any characters, any number of them, including none. So it will find aaa anywhere in the cell.

If this isn't suitable then let's get real world; put together and attach a workbook with just 2 columns of raw data:
  • a column with the full range of the kinds of instances where you want aaa to be counted, and some where you don't want them to be counted
  • a column with some numbers that need summing
then:
  • a column with your attempt(s) at a formula
  • a column indicating whether the row should be included in the summing or not
Reply With Quote
  #5  
Old 10-24-2021, 03:36 AM
Marcia's Avatar
Marcia Marcia is offline Use the > as text in formula Windows 10 Use the > as text in formula Office 2019
Expert
Use the > as text in formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you p45cal. Actually I made another copy of the workbook and changed all the ">" to "*" just so I could move on working but I prefer the ">" than the asterisk.
Please see the attached sample.
Attached Files
File Type: xlsx SUMIF_Greater than symbol.xlsx (13.5 KB, 6 views)
Reply With Quote
  #6  
Old 10-24-2021, 07:36 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Use the > as text in formula Windows 7 64bit Use the > as text in formula Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

I don't have the same functions as you ( in col D) but perhaps SUMPRODUCT helps ? ( see attached)
Attached Files
File Type: xlsx Copy of SUMIF_Greater than symbol-1.xlsx (12.8 KB, 6 views)
Reply With Quote
  #7  
Old 10-24-2021, 07:55 AM
p45cal's Avatar
p45cal p45cal is offline Use the > as text in formula Windows 10 Use the > as text in formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In the first instance try:
=SUMIF(Object,"*" & D3,$B$3:$B$13)
it means it'll 'ignore' the > symbol, but then it'll just find cells ending in aaa

if that fails then try:
If you have Office 365 try:
=SUM((Object=D3)*(Amount))
otherwise:
=SUMPRODUCT((Object=D3)*(Amount))
Reply With Quote
  #8  
Old 10-24-2021, 02:26 PM
Marcia's Avatar
Marcia Marcia is offline Use the > as text in formula Windows 10 Use the > as text in formula Office 2019
Expert
Use the > as text in formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you both. The SUMPRODUCT works but I chose the simplest code,
=SUM((Object=[@Object])*(Amount))
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use the > as text in formula SUM formula using text colours trevorc Excel 2 07-08-2021 09:19 PM
Help with Formula to extracting text from text string Haha88 Excel 7 01-13-2019 01:33 AM
Use the > as text in formula Help with Formula to extracting text from text string Haha88 Excel 9 02-05-2018 01:04 AM
Formula to Extract text from a text string Haha88 Excel 2 11-14-2017 01:32 AM
display text from formula ketanco Excel 1 04-24-2014 10:45 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft