sumproduct with range of numbers

[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

 

[T. Valko]

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.

 

[terri]

still not working.
how can i email you my spdsheet?

 

[T. Valko]

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

 

[terri]

goodness but i got it!!....
all numbers had to be in quotes....
thank you soooo very very much !!!

 

[T. Valko]

all numbers had to be in quotes....

Then that means your data is TEXT, not numeric.