SumIf vs array Sum - difference in calculations

DonAndress

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

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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
356
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
356

ADVERTISEMENT

Ok, thank you very much!
 

pgc01

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

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

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