# 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

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.

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

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1