CODEWITHSUNDEEP
excelsumexcel-formulalookup
Luke Richards
Luke Richards

Reputation: 45

Sum fields in a column if there is an entry in a corresponding row in another column

Assume the following data:

  | A        B        C
--+------------------------
1 | 2        3        5
2 | 2                 3
3 | 4                 4
4 |          2        3
5 |          5        6

In cell A6, I want Excel to add cells C1, C2, C3 on the basis that A1, A2 and A3 have data in. Similarly, I want B6 to add together C1, C4 and C5 because B1, B4 and B5 have data.

Can someone help?

Upvotes: 0

Views: 79

Answers (3)

cherdarchuk
cherdarchuk

Reputation: 46

A simple SUMIF formula will work

=SUMIF(A$1:A$5,"<>",$C$1:$C$5)

Place that formula is cell A6 and then copy it to B6.

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

In A6 enter:

=SUMPRODUCT(($C1:$C5)*(A1:A5<>""))

and then copy to B6:

enter image description here

Upvotes: 2

JimmyB
JimmyB

Reputation: 12610

You can create another column, e.g. AValue, with the formula =IF(ISBLANK(A1),0,A1) in it. This will return 0 if the cell in A in the corresponding line is empty, or the value from the cell in A otherwise.

Then you can just sum up the values of the new column.

Upvotes: 0

Related Questions