Sumproduct with numbers for criteria

JOHANNA

New Member
Joined
May 12, 2005
Messages
39
=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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

New Member
Joined
May 12, 2005
Messages
39
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.
 

Forum statistics

Threads
1,078,005
Messages
5,337,689
Members
399,160
Latest member
Vantone

Some videos you may like

This Week's Hot Topics

Top