SUMIF criteria help

[ExcelAtEverything]

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 %],"<>")

 

[etaf]

=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
	A	B	C	D	E	F	G	H	I
1								GS	GS1
2	SUMIFS	4						1	1
3	SUMIF	4						2
4								3
5
6
7								2	1
8								5
9
10
11								6
12								7
13
14
15								1	1
16								2
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMIFS(H2:H16,I2:I16,"<>")
B3	B3	=SUMIF(I2:I16,"<>",H2:H16)

 

[jasonb75]

=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

=SUMIFS([GS$],[GS Comp %],">-1e+100")

or for text criteria, try

=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]

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]

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

=SUMIFS([GS$],[GS Comp %],">-1e+100")

or for text criteria, try

=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!