ReginaAnn

Jul 15, 2024Copper Contributor

# 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") )`