Sumproduct with numbers for criteria

[JOHANNA]

=SUMPRODUCT(--(GURNEE!E124:E136="RETIRED"),--(GURNEE!F124:F136="-1"))

This formula won't calculate the # of retired people with the numerical -1. I changed my GURNEE sheet to show Neg One instead of the numeric, and changed the formula and it worked. Is there a way to keep the -1?

 

[Datsmart]

Will it work if you take the quotes off of the "-1"?

 

[JOHANNA]

Nope

 

[Datsmart]

I created a WorkSheet named GURNEE and input some data in E124:F136 to match your criteria.
The following formula works for me:
=SUMPRODUCT(--(GURNEE!E124:E136="RETIRED"),--(GURNEE!F124:F136=-1))

 

[Datsmart]

Another thought:
Are the numbers in F124:F136 numbers or text?
If you put the following formula in G124 and copy down, are they all TRUE?
=ISNUMBER(F124)

 

[JOHANNA]

Okay,
When I changed the column to specify "numbers" and took the quotations off of the -1 in my formula, it works.

Thanks! I didn't realize that was a problem.