SumIf vs array Sum - difference in calculations

DonAndress

Active Member
Joined
Sep 25, 2011
Messages
357
Hello.

I have a table like below:

Column D
Column E
F
G
H
% split key:255
719143636
% split key:255
2 77756139139

<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:
Code:
=SUMIF($D$7:$D$10,"<>""",F7:F10)+SUMIF($D$7:$D$10,"<>""",G7:G10)+SUMIF($D$7:$D$10,"<>""",H7:H10)
and array:
Code:
{=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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Sep 25, 2011
Messages
357
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Sep 25, 2011
Messages
357

ADVERTISEMENT

Ok, thank you very much!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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 ("")
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,517
Messages
5,832,217
Members
430,116
Latest member
d1pakjha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top