CODEWITHSUNDEEP
excelexcel-formulasum
Andreas
Andreas

Reputation: 85

Sum columns in Excel

I want to sum the columns B-D given that column A equals "far".

Does anyone have a smooth formula for this calculation?

The answer should read : 10+10+3+12+2+5 = 42

A    B   C   D
Far  10  10  3
Sol  10  21  12
Far  12  2   5
Sol  10  2   62
Gulf 10  4   0

Upvotes: 0

Views: 46

Answers (1)

Scott Craner
Scott Craner

Reputation: 152650

SUMPRODUCT:

=SUMPRODUCT(($A$2:$A$6="Far")*$B$2:$D$6)

enter image description here

SUMIF:

=SUMIF(A:A,"Far",B:B)+SUMIF(A:A,"Far",C:C)+SUMIF(A:A,"Far",D:D)

enter image description here

Both have their advantages and disadvantages.

SUMPRODUCT is shorter formula and easier to maintain but it is an array type formula and the references need to be limited to the data set.

SUMIF is not array but it requires the ranges to be the same size and as such one must do a SUMIF for each column and then sum the results.

Upvotes: 3

Related Questions