sumproduct with range of numbers
 

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



--
terri

sumproduct with range of numbers
 

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.

--
Biff
Microsoft Excel MVP


"terri" wrote in message
...
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



--
terri

sumproduct with range of numbers
 

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


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"terri" wrote in message
...
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



--
terri

sumproduct with range of numbers
 

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


--
Biff
Microsoft Excel MVP


"terri" wrote in message
...
still not working.
how can i email you my spdsheet?
--
terri


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"terri" wrote in message
...
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



--
terri

sumproduct with range of numbers
 

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


--
terri


"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"terri" wrote in message
...
still not working.
how can i email you my spdsheet?
--
terri


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"terri" wrote in message
...
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



--
terri

sumproduct with range of numbers
 

all numbers had to be in quotes....

Then that means your data is TEXT, not numeric.

--
Biff
Microsoft Excel MVP


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


--
terri


"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"terri" wrote in message
...
still not working.
how can i email you my spdsheet?
--
terri


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"terri" wrote in message
...
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



--
terri