Forum Discussion
SUM If with multiple Not equal to criteria.
I am having trouble with the formula below. The first part works perfectly. It is when I get to the is not equal to part that the formula does not work. =sumif('APXBalances'!E:E,'APXBalances'!...
ReginaAnn , I'm sorry.
That's not the space. SUMIFS, as any other someIF function (e.g. COUNTIFS), internally converts texts , which looks like numbers, into numbers and compare them. For example, "0575" will be compared as 575.
Trick is to add CHAR(173)
=SUMIFS(APXBalances!E:E, APXBalances!B:B, 24, APXBalances!A:A, CHAR(173) & "0735") -SUMIFS(APXBalances!E:E, APXBalances!C:C,"<>", APXBalances!C:C,"<>" & CHAR(173) & "00000", APXBalances!C:C, "<>" & CHAR(173) & "05757")or use SUMPRODUCT
=SUMPRODUCT(APXBalances!E2:E1000 * (APXBalances!B2:B1000 = 24) * (APXBalances!A2:A1000 = "0735") ) - SUMPRODUCT(APXBalances!E2:E1000 * (APXBalances!C2:C1000 <> "") * (APXBalances!C2:C1000 <> "00000") * (APXBalances!C2:C1000 <> "05757") )
Forgot the file, attached
- Thanks for the reply Sergei,
Unfortunately that did not work. Excel ignores that not equal to <>. Total in your spreadsheet, -105,246.84 is the sum of all items. I have tried to wrap this equation in a sum function, but that didn't work either. Let me know if you have any other suggestions. Thanks, ReginaReginaAnn , I'm sorry.
That's not the space. SUMIFS, as any other someIF function (e.g. COUNTIFS), internally converts texts , which looks like numbers, into numbers and compare them. For example, "0575" will be compared as 575.
Trick is to add CHAR(173)
=SUMIFS(APXBalances!E:E, APXBalances!B:B, 24, APXBalances!A:A, CHAR(173) & "0735") -SUMIFS(APXBalances!E:E, APXBalances!C:C,"<>", APXBalances!C:C,"<>" & CHAR(173) & "00000", APXBalances!C:C, "<>" & CHAR(173) & "05757")or use SUMPRODUCT
=SUMPRODUCT(APXBalances!E2:E1000 * (APXBalances!B2:B1000 = 24) * (APXBalances!A2:A1000 = "0735") ) - SUMPRODUCT(APXBalances!E2:E1000 * (APXBalances!C2:C1000 <> "") * (APXBalances!C2:C1000 <> "00000") * (APXBalances!C2:C1000 <> "05757") )- Hi Sergei, This was magical! Thank you so much for your help!
