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'!B:B=24,'APXBalances'!A:A"0735")-SUMIFS('APXBalances'!C:C,"<>",'APXBalances'!C:C,"<>00000",'APXBalances'!C:C"<>05757")
Here is an example of my data
| Fund | AY | APPN | Remaining Cash Balance Budget | Appropriation Cash Available | Appropriation Accrued Cash Available |
| 0735 | 22 | 0 | 52623.42 | 52623.42 | |
| 0735 | 22 | 00000 | 0 | 14899.98 | 14899.98 |
| 0735 | 22 | 05755 | 0 | 0 | 0 |
| 0735 | 22 | 05862 | 0 | 37723.44 | 37723.44 |
| 0735 | 22 | 13001 | 0 | 0 | 0 |
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") )
6 Replies
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") )
Perhaps you mean something like
=SUMIFS(APXBalances!E:E, APXBalances!B:B, 24, APXBalances!A:A,"0735") -SUMIFS(APXBalances!E:E, APXBalances!C:C,"<>", APXBalances!C:C,"<>00000", APXBalances!C:C, "<>05757")
