SumIf vs array Sum - difference in calculations

[DonAndress]

Hello.

I have a table like below:

Column D	Column E	F	G	H
	% split key:	2	5	5
719		14	36	36
	% split key:	2	5	5
2 777		56	139	139

<tbody>
</tbody>

I'd like to sum values from col. F, G and H only if in col D there is a value.

I use two different ways of summing the values:
straight forward:

=SUMIF($D$7:$D$10,"<>""",F7:F10)+SUMIF($D$7:$D$10,"<>""",G7:G10)+SUMIF($D$7:$D$10,"<>""",H7:H10)

and array:

{=SUM(IF(D7:D10<>"",F7:H10,""))}

But only array formula works correctly.
In the first example I get a sum of 444 and in the second it's 420 which is correct.

Could you please tell me what did I do wrong in the first example?

 

[Andrew Poulsom]

This works for me:

=SUMIF($D$7:$D$10,"<>",F7:F10)+SUMIF($D$7:$D$10,"<>",G7:G10)+SUMIF($D$7:$D$10,"<>",H7:H10)

 

[DonAndress]

Hi Andrew, thanks for your reply.

Ok, this little change works.
But why?

It's not straight forward condition taken from IF into SUMIF?
For the future, how should I tweak conditions so they would work in SUMIF?

 

[Andrew Poulsom]

That's how SUMIF works. I agree that the conditions for non blanks and blanks is not very intuitive. Blanks would be:

=SUMIF($D$7:$D$10,"=",F7:F10)

 

[DonAndress]

Ok, thank you very much!

 

[pgc01]

That's how SUMIF works. I agree that the conditions for non blanks and blanks is not very intuitive. Blanks would be:

=SUMIF($D$7:$D$10,"=",F7:F10)

Just to complement, because "blank" is a confusing word in excel

The formula

=SUMIF(E4:E7,"=",F4:F7)

checks for empty cells, while

=SUMIF(E4:E7,"",F4:F7)

checks for empty cells or cells with null strings ("")

 

[Andrew Poulsom]

Thanks for elaborating Pedro.

 

[pgc01]

My pleasure, Andrew.

I remember that I have had problems in the past with the word "blank" in the excel documentation and so I'm kind of afraid of it now.