SumProduct over Range that includes text

1
votes

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:

  • =SUMPRODUCT(--(C2:C10="Mike"),A1:B10)
  • =SUMPRODUCT((C2:C10="Mike")*(A1:B10))
  • =SUMPRODUCT(A1:B10,C2:C10="Mike")

Any help is appreciated, Thanks!

1
excelexcel-2010
Is there a reason for not using =SUMIF(C2:C10,"=Mike",A:A) Where C is the column of names and A is column of values? – user2140261
I don't think SUMIF will work here because the range to sum is two columns while the criteria range is a single column - SUMIF works on a one-to-one basis only - hence my suggestions....you could, of course use two SUMIF functions added together..... – barry houdini

1 Answers

4
votes

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