CODEWITHSUNDEEP
excelexcel-2010
zack
zack

Reputation: 5415

SumProduct over Range that includes text

I'm trying to use a sumproduct formula over a range (e.g., A2:B10) given that the corresponding cell in column C = "Mike" - I keep getting #value errors thrown at me because there is text in some cells in the A2:B10 range.

I'm looking for the sum of the numbers(assuming it's a number) in each row with "Mike" in column C.

Formulas I've tried:

Any help is appreciated, Thanks!

Upvotes: 1

Views: 14927

Answers (1)

barry houdini
barry houdini

Reputation: 46401

Try this formula with SUMPRODUCT

=SUMPRODUCT(A2:B10,(C2:C10="Mike")*ISNUMBER(A2:B10))

or you can use an "array formula"

=SUM(IF(C2:C10="Mike",A2:B10))

that latter formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

Upvotes: 5

Related Questions