# SUMIFS/COUNTIFS array function question (multiple criteria)

1. ## SUMIFS/COUNTIFS array function question (multiple criteria)

Hi I have a simple question:

I know how to use SUMIFS as an array but don't know how to make it work when I do not want to include certain values in the sum:

EXAMPLE: How can I convert =SUMIFS(\$A:\$A,\$N:\$N,{"*Criteria 1*","*Criteria 2*","*Criteria 3*"}) into something like this: SUMIFS(\$A:\$A,\$N:\$N,{"<>"&"*Criteria 1*","<>"&"*Criteria 2*","<>"&"*Criteria 3*"}). The second formula does not work! How can I make it work for text?

Also I want to sum something not equal to a cell that contains specific text or numbers:
Another example is this: How to convert the statement below into a SUMIFS of A:A where N:N does NOT contain 1,2,3,4,5 etc...
=SUMIFS(\$A:\$A,\$N:\$N,{1,2,3,4,5,6}) - ????

AK  Register To Reply

2. ## Re: SUMIFS/COUNTIFS array function question (multiple criteria)

Why is it necessary to express the group of criterion as an array?

This works just as effectively.

=SUMIFS(A:A,N:N,"<>"&1,N:N,"<>"&2,N:N,"<>"&3,N:N,"<>"&4,N:N,"<>"&5,N:N,"<>"&6)

If you do require the use of a nested array, you may wish to use SUMPRODUCT instead.  Register To Reply

3. ## Re: SUMIFS/COUNTIFS array function question (multiple criteria)

For the positive result, e.g. sum column A when N = any of 1,2,3,4,5,6 then you need to wrap a SUM function around the SUMIFS, i.e.

=SUM(SUMIFS(\$A:\$A,\$N:\$N,{1,2,3,4,5,6}))

For excluding those values you could just subtract that amount from the SUM of column A, like this:

=SUM(\$A:\$A)-SUM(SUMIFS(\$A:\$A,\$N:\$N,{1,2,3,4,5,6}))

or you can explicitly exclude them as separate conditions like this:

=SUMIFS(\$A:\$A,\$N:\$N,"<>1",\$N:\$N,"<>2",\$N:\$N,"<>3",\$N:\$N,"<>4",\$N:\$N,"<>5",\$N:\$N,"<>6")

Both of those approaches can be used with text values too  Register To Reply

4. ## Re: SUMIFS/COUNTIFS array function question (multiple criteria)

Thank you so much!

I totally had forgotten about wrapping everything with the SUM function! details, details...

Have a good one!  Register To Reply