CODEWITHSUNDEEP
excelexcel-formula
guenoon
guenoon

Reputation: 35

In a column if a cell is empty how to look to another column

I want to do a sumifs but for one column if a cell is empty then it checks the value of the previous column.

    sumifs(A:A;C:C,"ccc")

In this example if there is no value in the column C, I want the condition to check the column B.

A    B    C

1    a    a

5    a    a

8    v    v

1    v    v

6    a

3    a     a

Here sumif(A:A;C:C;"a") does the sum of the column A if in the column C there is an "a" but if there is a blank I want to check the column B if there is an "a"

Is it possible ?

Upvotes: 0

Views: 671

Answers (1)

OverflowStacker
OverflowStacker

Reputation: 1348

This version would be with SUMPRODUCT. It would also be possible with SUMIF's but I'm not using it very much, so this is easier for me. :)

=SUMPRODUCT(((A3:A13)*(C3:C13="a"))+((A3:A13)*(C3:C13="")*(B3:B13="a")))

The part in front of + adds all values in column A when there is an "a" in column C. The part after + adds all values in column A when there is an "a" in column B and a blank cell in column C.

enter image description here

Upvotes: 0

Related Questions