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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT 3
Macro to copy value in field and insert it into filter 2
SUMPRODUCT again! 5
Help with SUMPRODUCT & converting date to value 1
Cannot Get ListBox .ItemsSelected, help! 1
Specialized custom number format 4
Count unique field1 combined with count field2, both grouped andungrouped 3
dcount of date range in report not working 7

Back
Top