SUMIF on multiple cells if first cell ISBLANK?

[CrazyFox]

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

[Lupp]

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.

[CrazyFox]

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