sumproduct with range of numbers

  • Thread starter Thread starter terri
  • Start date Start date
T

terri

i'm tired :(
please help.
i have a list of hospital accounts with codes for seven hospitals.
i need a summary sheet to count a range of codes for each hospital.
i'm using:
=SUMPRODUCT(--('Jan
08'!A7:A150="1"),--(G7:G150>=--"707"),--(G7:G150<=--"707.9"))

1=hospital number 1
the codes are a range from 707 to 707.9

i can get it to look for one single number, but not a range. :(


i get the first part to work
 
Are these ranges all on the same sheet?

Try one of these:

=SUMPRODUCT(--('Jan 08'!A7:A150="1"),--('Jan 08'!G7:G150>=707),--('Jan
08'!G7:G150<=707.9))

=SUMPRODUCT(--('Jan 08'!A7:A150=1),--('Jan 08'!G7:G150>=707),--('Jan
08'!G7:G150<=707.9))

When you quote numbers, "1", Excel evaluates that as the TEXT entry "1" and
not the number 1.
 
...can i email you my spdsheet?

If it's <1mb in size. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.
 
goodness but i got it!!....
all numbers had to be in quotes....
thank you soooo very very much !!!
 
all numbers had to be in quotes....

Then that means your data is TEXT, not numeric.
 
Back
Top