Sum row based on criteria across multiple columns

Question

I have googled for hours, not being able to find a solution to what I need/want. I have an Excel sheet where I want to sum the values in one column based on the criteria that either one of two columns should have a specific value in it. For instance

    A     B     C
1   4    20     7
2   5   100     3
3  100   21     4
4   15   21     4
5   21   24     8

I want to sum the values in C given that at least one of A and B contains a value of less than or equal to 20. Let us assume that A1:A5 is named A, B1:B5 is named B, and C1:C5 is named C (for simplicity). I have tried:

={SUMPRODUCT(C,((A<=20)+(C<=20)))}

which gives me the rows where both columns match summed twice, and

={SUMPRODUCT(C,((A<=20)*(C<=20)))}

which gives me only the rows where both columns match

So far, I have settled for the solution of adding a column D with the lowest value of A and B, but it bugs me so much that I can't do it with formulas.

Any help would be highly appreciated, so thanks in advance. All I have found when googling is the "multiple criteria for same column" problem.

Answer 1

Your problem that it is "summing twice" in this formula

={SUMPRODUCT(C,((A<=20)+(C<=20)))}

is due to addition turning first TRUE plus the second TRUE into 2. It is not actually summing twice, because for any row, if only one condition is met, it would count that row only once.

The solution is to transform either the 1 or the 2 into a 1, using an IF:

={SUMPRODUCT(C,IF((A<=20)+(C<=20))>0, 1, 0)}

That way, each value in column C would only be counted at max once.

Answer 2

Thanks. That works. Found another one that works, after I figured out that excel does not treat 1 + 1 = 1 as I learnt in discrete mathematics, but as you say, counts the both the trues. Tried instead with:

{=SUM(IF((A<=20)+(B<=20);C;0))}

But I like yours better.

Answer 3

If you plan to use a large set of data then it is best to use the array formula:

{=SUM(IF((A1:A5<=20)+(B1:B5<=20),C1:C5,0))}

Obviously adjust the range to suit the data set, however if the whole of each column is to form part of the formula then you can simply adjust to:

{=SUM(IF((A:A<=20)+(B:B<=20),C:C,0))}

This will perform the calculation on all rows of data within the A, B and C columns. With either example remember to press Ctrl + Shift + Enter in order to trigger the array formula (as opposed to typing the { and }).

Answer 4

Following this site you could build up your SUMPRODUCT() formula like this:

=SUMPRODUCT(C,SIGN((A<=20)+(C<=20)))

So, instead of a nested IF() you control your or condition with the SIGN()function.

hth