SOLVED

# SUM If with multiple Not equal to criteria.

Copper 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
6 Replies

# Re: SUM If with multiple Not equal to criteria.

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

# Re: SUM If with multiple Not equal to criteria.

Forgot the file, attached

# Re: SUM If with multiple Not equal to criteria.

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, Regina
best response confirmed by ReginaAnn (Copper Contributor)
Solution

# Re: SUM If with multiple Not equal to criteria.

@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.

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

# Re: SUM If with multiple Not equal to criteria.

Hi Sergei, This was magical! Thank you so much for your help!

# Re: SUM If with multiple Not equal to criteria.

1 best response

Accepted Solutions
best response confirmed by ReginaAnn (Copper Contributor)
Solution

# Re: SUM If with multiple Not equal to criteria.

@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.

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