#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)) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 |