CODEWITHSUNDEEP
arraysexcelsumifs
Dion
Dion

Reputation: 203

Excel SUMIF where criteria is in array

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?

Upvotes: 1

Views: 1535

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

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.

Upvotes: 6

Related Questions