CODEWITHSUNDEEP
excelexcel-2007excel-formula
nigel carter
nigel carter

Reputation: 63

SUMIFS Formula that would check multiple columns

I have an excel sheet and am attempting to incorporate a SUMIFS formula which would check one column for an condition then check another group of columns to see in the match a condition in order to sum all the values which are > 50000 in the group of columns then multiply those values by 0.084. My formula returns the #VALUE error. How can I achieve what i want?

A     B     C     D     E     F     G     H     I

NO                    51000 52000 12000 10000 53000

NO                    23000 48000 54000 55000 50000

=SUMIFS(E10:I610,A10:A610,"No",E10:I610,">50000")

Help please...

Upvotes: 3

Views: 8543

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33474

=SUM(IF(A10:A610="N",1,0)*IF(E10:I610>50000,1,0)*E10:I610)

The above when used as array formula (by pressing ctrl+shift+enter, instead of enter) works too.

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27478

You can switch it to a SUMPRODUCT formula, which can handle the multiple columns:

=SUMPRODUCT(E10:I610*(A10:A610="No")*(E10:I610>50000))

EDIT:

Per JosieP, this will work even if there are text values in the range:

SUMPRODUCT(E10:I610,(A10:A610="No")*(E10:I610>50000))

Upvotes: 5

Related Questions