# SUMIF on multiple cells if first cell ISBLANK?

Hi,

I'm looking for the formula to have every cell that contains a specific word then add the result if the first cell of the row is Blank. So in the exemple I want to have the sum of all the apple where the first row ISBLANK

    A       B         C       D       E
1         Apple       60    Orange    80
2   X     Pear        50    Apple     70
3         Banana      30    Pear      25
4   X     Apple       50    Banana    35


For now I have this {=SUMIF(B1:D4,"Apple",C1:E4)} That gives 60+70+50=180 since it doesn't look for the first blank column But I tried multiple things like sumifs or nested if with the first column and it spreads the cells down or even worse multiple #504 errors

180
0
180
0

Err:504 Err:504 Err:504
Err:504 Err:504 Err:504
Err:504 Err:504 Err:504
Err:504 Err:504 Err:504


Any idea how to achieve this ?

cheers

edit retag close merge delete

Sort by » oldest newest most voted

The arrangement of data is really bad. As long as you only have two pairs of columns (Item | Number), you can use something like
=SUMPRODUCT(C1:C4;B1:B4="Apple";$A1:$A4="")+SUMPRODUCT(E1:E4;D1:D4="Apple";$A1:$A4="").

I would prefer to use a helper column with the formula
=IF(A1="";C1*(B1="Apple")+E1*(D1="Appöe");0)
in the first row and the filled down as far as needed.

To include the item columns with the SUM range I would deprecate anyway.

more

Thanks for the fast answer. Yeah I know the arrangement is far from ideal. I had the SUMPRODUCT as well but I had 5 columns so I wanted to avoid doing it 5 times on each line as it's calculated on another sheet numerous times. I was thinking to rearrange the data ...

cheers

( 2018-01-06 01:28:22 +0100 )edit