CODEWITHSUNDEEP
excelexcel-formulaarray-formulas
gudal
gudal

Reputation: 337

Sum row based on criteria across multiple columns

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.

Upvotes: 1

Views: 5292

Answers (4)

wellimustbedead
wellimustbedead

Reputation: 91

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 }).

Upvotes: 0

m0r0dan
m0r0dan

Reputation: 149

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

Upvotes: 0

gudal
gudal

Reputation: 337

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.

Upvotes: 2

josh waxman
josh waxman

Reputation: 211

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.

Upvotes: 1

Related Questions