#1




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. 
#2




Perhaps inserting a tlde would help?
"~>"&"aaa" 
#3




I tried that tilde but the formula is still adding the values of all accounts with > in column A.

#4




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:

#5




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. 
#6




I don't have the same functions as you ( in col D) but perhaps SUMPRODUCT helps ? ( see attached)

#7




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)) 
#8




Thank you both. The SUMPRODUCT works but I chose the simplest code,
=SUM((Object=[@Object])*(Amount)) 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
SUM formula using text colours  trevorc  Excel  2  07082021 09:19 PM 
Help with Formula to extracting text from text string  Haha88  Excel  7  01132019 01:33 AM 
Help with Formula to extracting text from text string  Haha88  Excel  9  02052018 01:04 AM 
Formula to Extract text from a text string  Haha88  Excel  2  11142017 01:32 AM 
display text from formula  ketanco  Excel  1  04242014 10:45 AM 