# SumIf vs array Sum - difference in calculations

#### DonAndress

##### Active Member
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:
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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)

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?

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)

Ok, thank you very much!

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 ("")

Thanks for elaborating Pedro.

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.

Replies
2
Views
181
Replies
6
Views
251
Replies
0
Views
162
Replies
4
Views
130
Replies
22
Views
774

1,219,829
Messages
6,150,475
Members
450,967
Latest member
itzwinger

### 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.

### Which adblocker are you using?

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

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