Sumproduct

[robot]

I have a worksheet with data in the region C1:Z10.
I want to sum over everything in the region C1:Z10 which meets certain
criteria for columns A & B (more specifically: equal to 1).

Also, I don't want to create an extra column of sums for the region. All I
want is a formula.

I know I can use
SumProduct((A1:A10=1)*(B1:B10=1), C1:C10 + D110 +...) , but that's too
clumsy.

What can I do? Suggestions are most welcome!

 

[Bob Phillips]

=SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(C1:Z10))

 

[Mike H]

Hi,

I'm not sure I fully understand but maybe this

=SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(C1:Z10))

Mike

 

[robot]

The formula works! Thank you both for your replies.

However, some of the cells in the region C1:Z10 include text but not
numbers. I would like to treat them as zeros, so I tried

=SUMPRODUCT((A1:A10=1)*(B1:B10=1)*(IF(ISNUMBER(C1:Z10), C1:Z10, 0))

However, the formula always returns zero. What can I do?

 

[Bob Phillips]

=SUM(IF(ISNUMBER(C1:Z10),IF((A1:A10=1)*(B1:B10=1),C1:Z10,0)))

as an array formula, commit with Ctrl-Shift-Enter

 

[robot]

Thank you Bob, you are a great help.

While your formula works, I am still a little uncomfortable about the use of
arrays of different dimensions (A1:A10 and C1:Z10) in a single array
formula. It is because according to the Excel XP help file, all arrays in an
array formula should have the same dimensions.

 

[Bob Phillips]

Yet you were quite happy to use it with SUMPRODUCT, which is effectively an
array formula, and the help makes the same statement about its dimensions,
albeit in different words.

 

[robot]

I'll try getting used to this new concept. Thanks again, Bob.