Ask Your Question
0

SUMIF on multiple cells if first cell ISBLANK?

asked 2018-01-05 21:43:59 +0100

CrazyFox gravatar image

updated 2018-01-05 21:46:39 +0100

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-01-05 22:50:33 +0100

Lupp gravatar image

updated 2018-01-05 22:52:11 +0100

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.

edit flag offensive delete link more

Comments

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

CrazyFox gravatar imageCrazyFox ( 2018-01-06 01:28:22 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-05 21:43:59 +0100

Seen: 83 times

Last updated: Jan 05 '18