Sumproduct & Date multiple criteria
 

I need to count items that equal a number(B3) range name is PHYNO, and = a
type "INPT" (range name is TYPE), and are greater than 1/1/2008(AH2). The
date is in range name SURGDT and is formatted as *m/d/yyyy. When I try to do
my formula, I get 0. This is the formula I've tried:
=SUMPRODUCT((SURGDT=AH2)*(PHYNO=B3)*(TYPE="INPT") )

Hoping someone can help...

Sumproduct & Date multiple criteria
 

Try this

=SUMPRODUCT((SURGDT=Date(year(AH2),month(AH2),day (AH2)))*(PHYNO=B3)*(TYPE="INPT"))

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Tasha" wrote:

I need to count items that equal a number(B3) range name is PHYNO, and = a
type "INPT" (range name is TYPE), and are greater than 1/1/2008(AH2). The
date is in range name SURGDT and is formatted as *m/d/yyyy. When I try to do
my formula, I get 0. This is the formula I've tried:
=SUMPRODUCT((SURGDT=AH2)*(PHYNO=B3)*(TYPE="INPT") )

Hoping someone can help...

Sumproduct & Date multiple criteria
 

Your formula works perfectly.

Make sure that each of your named ranges contain the same number of cells.

Tom

Sumproduct & Date multiple criteria
 

Also,

The named range cannot be an entire column (e.g. "A:A") but must include
rows (e.g. "A1:A65536" or "B1:B12").

Tom

Sumproduct & Date multiple criteria
 

Thanks Barb!! I used your formula, but now am getting #N/A!. Also, should
have mentioned, my range names are a formula so they are dynamic, which is as
follows:

PHYNO =OFFSET(all07_08!$F$2,0,0,COUNTA(all07_08!$F:$F),1 )
SURGDT =OFFSET(all07_08!$A$2,0,0,COUNTA(all07_08!$A:$A),1 )
TYPE =OFFSET(all07_08!$C$2,0,0,COUNTA(all07_08!$C:$C),1 )



"Barb Reinhardt" wrote:

Try this

=SUMPRODUCT((SURGDT=Date(year(AH2),month(AH2),day (AH2)))*(PHYNO=B3)*(TYPE="INPT"))

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Tasha" wrote:

I need to count items that equal a number(B3) range name is PHYNO, and = a
type "INPT" (range name is TYPE), and are greater than 1/1/2008(AH2). The
date is in range name SURGDT and is formatted as *m/d/yyyy. When I try to do
my formula, I get 0. This is the formula I've tried:
=SUMPRODUCT((SURGDT=AH2)*(PHYNO=B3)*(TYPE="INPT") )

Hoping someone can help...

Sumproduct & Date multiple criteria
 

I used Barb's formula, but now am getting #N/A!. Also, should have
mentioned, my range names are a formula so they are dynamic, which is as
follows:

PHYNO =OFFSET(all07_08!$F$2,0,0,COUNTA(all07_08!$F:$F),1 )
SURGDT =OFFSET(all07_08!$A$2,0,0,COUNTA(all07_08!$A:$A),1 )
TYPE =OFFSET(all07_08!$C$2,0,0,COUNTA(all07_08!$C:$C),1 )


"TomPl" wrote:

Your formula works perfectly.

Make sure that each of your named ranges contain the same number of cells.

Tom

Sumproduct & Date multiple criteria
 

I tested again and find that it work fine on the condition that each of your
ranges is the same size. If columns A, C and F have data in exactly the same
rows it works. If data is missing from one cell you get unequal range sizes
and the formula does not work. If you know that one column will contain a
complete set of data while the other two don't you might change your formula:

Assume SURGDT will always have a date even though there may be blanks in
PHYNO and TYPE. Consider these range names:
PHYNO =OFFSET(all07_08!$F$2,0,0,COUNTA(all07_08!$A:$A),1 )
SURGDT =OFFSET(all07_08!$A$2,0,0,COUNTA(all07_08!$A:$A),1 )
TYPE =OFFSET(all07_08!$C$2,0,0,COUNTA(all07_08!$A:$A),1 )

I haven't tested this but I think it should work. This is a rather fragile
setup you have.

Tom

Sumproduct & Date multiple criteria
 

Yup, that worked based on the assumption that there is a date in column A
without any skipped rows. I think this would be more solid logic if you
could use:

PHYNO =all07_08!$F$2:$F65535
SURGDT =all07_08!$A$2:$A65535
TYPE =all07_08!$C$2:$C65535


I hope that helps.

Sumproduct & Date multiple criteria
 

Thanks!! I changed my range names to the way you showed it and it is working
now. Thanks so much!!!

"TomPl" wrote:

Yup, that worked based on the assumption that there is a date in column A
without any skipped rows. I think this would be more solid logic if you
could use:

PHYNO =all07_08!$F$2:$F65535
SURGDT =all07_08!$A$2:$A65535
TYPE =all07_08!$C$2:$C65535


I hope that helps.