SUMIF criteria help

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows
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 %],"<>")

1620465313946.png
 

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
Joined
Oct 24, 2012
Messages
4,752
Office Version
  1. 365
Platform
  1. MacOS
=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
Joined
Dec 30, 2008
Messages
12,930
Office Version
  1. 365
Platform
  1. Windows
=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.
 
Solution

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,752
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

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