# SUMIF criteria help

#### ExcelAtEverything

##### Active Member
Hello,
Could someone help me with my SUMIF criteria?
What I want is: Sum as many items in the GS\$ column as their are in the GS Comp % (Like vs Like) column.
The problem is that there are formulas in each of these cells, and my SUMIF below is counting the formulas that are in the "empty" cells in column I as values. So instead of this summing only G16 & G17, it's summing G16:G19. I've tried a few alternatives after some internet searches, but can't seem to get it to work. Is the answer to just do a combination If/SUMPRODUCT formula (since if not not mistaken, SUMPRODUCT does not recognize formulas as values)?

Thanks!

=SUMIFS([GS\$],[GS Comp %],"<>")

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### etaf

##### Well-known Member
=SUMIF(I2:I16,"<>",H2:H16)
Works
as does
=SUMIFS(H2:H16,I2:I16,"<>")

Is it your NAME Range, TABLE naming ?
Try replacing with Column Range References in the real data and see if that works

Book1
ABCDEFGHI
1GSGS1
2SUMIFS411
3SUMIF42
43
5
6
721
85
9
10
116
127
13
14
1511
162
Sheet1
Cell Formulas
RangeFormula
B2B2=SUMIFS(H2:H16,I2:I16,"<>")
B3B3=SUMIF(I2:I16,"<>",H2:H16)

#### jasonb75

##### Well-known Member
=SUMIF(I2:I16,"<>",H2:H16)
Works
as does
=SUMIFS(H2:H16,I2:I16,"<>")
Neither of them will work, Wayne.
The problem is that there are formulas in each of these cells, and my SUMIF below is counting the formulas that are in the "empty" cells in column I as values.
As the OP has pointed out, using "<>" as the criteria incorrectly includes formula blanks in the results.

@ExcelAtEverything
Cells that contain formulas are not "empty" they are "blank". There is a difference.

For numeric criteria, try changing the formula to
Excel Formula:
``=SUMIFS([GS\$],[GS Comp %],">-1e+100")``
or for text criteria, try
Excel Formula:
``=SUMIFS([GS\$],[GS Comp %],"?*")``
if not not mistaken, SUMPRODUCT does not recognize formulas as values
All formulas recognise the value as a value regardless of whether it is typed into the cell or the result of another formula. The exceptions being those that use the cells / ranges as reference points rather than looking at the contents.

#### etaf

##### Well-known Member
Neither of them will work, Wayne.
ExcelAtEverything said:
The problem is that there are formulas in each of these cells, and my SUMIF below is counting the formulas that are in the "empty" cells in column I as values.
As the OP has pointed out, using "<>" as the criteria incorrectly includes formula blanks in the results.
Thanks for pointing that out , my mistake

#### ExcelAtEverything

##### Active Member
Neither of them will work, Wayne.

As the OP has pointed out, using "<>" as the criteria incorrectly includes formula blanks in the results.

@ExcelAtEverything
Cells that contain formulas are not "empty" they are "blank". There is a difference.

For numeric criteria, try changing the formula to
Excel Formula:
``=SUMIFS([GS\$],[GS Comp %],">-1e+100")``
or for text criteria, try
Excel Formula:
``=SUMIFS([GS\$],[GS Comp %],"?*")``

All formulas recognise the value as a value regardless of whether it is typed into the cell or the result of another formula. The exceptions being those that use the cells / ranges as reference points rather than looking at the contents.
Thank you for the detailed answer, jasonb75! Great info. Works great!

Replies
7
Views
103
Replies
7
Views
128
Replies
2
Views
109
Replies
3
Views
314
Replies
5
Views
269

1,132,981
Messages
5,656,214
Members
418,290
Latest member
ArrArkRE

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