Excel SUMIF where criteria is in array

Question

Is it possible to SUMIF a range where the criteria is not in an array?

For example, I have a list of names in column A and corresponding values in column B. I've created a named range (rngExcludedNames) of those names I want to exclude from the result. I tried the following with no luck:

=SUMIF(Sheet1!A1:A100,COUNTIF(rngExcludedNames,Sheet1!A1:A100)<1,Sheet1!B1:B100)

Any suggestions?

Did my answer work for you? Don't forget to mark it as correct. — Scott Craner
– Scott Craner, Commented Mar 14, 2016 at 20:40
Apologies, I was working on another project and have just come back to this one. Your solution worked a treat - I'll mark it correct. Thanks for your help. — Dion
– Dion, Commented Mar 18, 2016 at 21:49

Scott Craner · Accepted Answer · 2016-03-03 22:15:33Z

6

You were close, the SUMPRODUCT() would be a better fit.

Use this formula:

=SUMPRODUCT((COUNTIF(rngExcludedNames,Sheet1!$A$1:$A$100)=0)*(Sheet1!$B$1:$B$100))

SUMPRODUCT() is an array formula. As it iterates through the ranges, in this case it is the two on the Sheet1, it tests each one against the criteria range rngExcludedNames.

So it effectively runs the countIf 100 times, each with different cell in the second criterion. If it returns 0 it is true and returns 1. To which we multiply the value in Column B on the same row as it is iterating with column A. 1 * value = value and 0 * value = 0. As it iterates it sums each result.

edited Mar 3, 2016 at 22:15

answered Mar 3, 2016 at 20:42

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mat Richardson Over a year ago

How does the COUNTIF portion of this work? I've not seen it used like this before..

Scott Craner Over a year ago

You are asking me how Microsoft thinks? SUMPRODUCT is an array formula. As it iterates through the ranges, in this case it is the two on the Sheet1, it tests each one against the criteria range rngExcludedNames. So it effectively runs the countIf 100 times, each with different cell in the second criterion. If it returns 0 it is true and returns 1.

Collectives™ on Stack Overflow

Excel SUMIF where criteria is in array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related