Problem with SUMPRODUCT formula

[Guest]

I got this to work in another workbook, but won't work in this one....can
someone help me figure out what is wrong?

A little Background first:
workbook: September MTD Stats.xls
worksheet: cnsdlywrksht Defined Names:CNSDAY, HSV, F/C
worksheet: PATIENT_DAYS

layout of cnsdlywrksht:
A B C D E F G
RM PATNO PATIENT NAME CNSDAY STAY F/C HSV
161 178 xxxxxJANE DOE 1 11 V IPS
162 135 xxxx BRENDA 5 8 D IPM
OB2 138 xxxx PAT 1 2 S IPO
163 137 xxxxxxJOHN 5 5 D IPM

*CNSDAY is date 09/01/07 formatted as day, displays as 1

layout of PATIENT_DAYS:
A B C D E F G H I J
PAT DAYS 1 2 3 4 5 6 7 8 9 etc... <---(date form as day)
(row 3 is blank)
IPM 2
IPO 1
IPS 1
etc...

layout above is hard to show, but under patient day 1(09/01/07) column, in
cell next to IPM, I want it to give me the total number of patients from the
worksheet cnsdlywkrsht that have a census date of 09/01/07 and a HSV code of
IPM. I use this on another workbook and it works fine, but keep getting #N/A
error on this one for some reason.

The defined names are set up as follows:
CNSDAY - =OFFSET(cnsdlywrksht!$D$2,0,0,COUNTA(cnsdlywrksht!$D:$D),1)
HSV - =OFFSET(cnsdlywrksht!$G$2,0,0,COUNTA(cnsdlywrksht!$G:$G),1)
F/C - =OFFSET(cnsdlywrksht!$F$2,0,0,COUNTA(cnsdlywrksht!$F:$F),1)

Sorry this was so long, Please help????? Thanks so much!

 

[Arvi Laanemets]

Hi

At first, I assume you want to use named ranges in SUMPRODUCT, do you? Then
all those ranges MUST ALWAYS be of same dimension!!! To be sure of this, you
have to define them like this:
CNSDAY = OFFSET(cnsdlywrksht!$D$2,0,0,COUNTA(cnsdlywrksht!$D:$D),1)

HSV = OFFSET(cnsdlywrksht!$G$2,0,0,COUNTA(cnsdlywrksht!$D:$D),1)

F/C = OFFSET(cnsdlywrksht!$F$2,0,0,COUNTA(cnsdlywrksht!$D:$D),1)
{i.e.COUNTA(...) part of formula refers always to same column}

On sheet PATIENT_DAYS, dates are in row 2, are they?

Now, on sheet PATIENT_DAYS, into cell B4 enter
= SUMPRODUCT(--(CNSDAY=B$2),--(HSV=$A4))
, and copy it to range bordered by dates in header and HSV-values.

When you want empty cells when 0, then staring formula will be
=
IF(SUMPRODUCT(--(CNSDAY=B$2),--(HSV=$A4))=0,"",SUMPRODUCT(--(CNSDAY=B$2),--(HSV=$A4)))


Arvi Laanemets

 

[Guest]

Nevermind, I figured out what the problem was....after 2 hours of searching.
I was testing my cnsdlywrksht and keyed in the dates as just the day, so had
it formatted as 01/15/1900

 

[Guest]

Thanks for your reply, I realized that the date was formatted wrong on some
of my cells, so after reformatting, it is working fine now....Thanks for your
help as always!!!!!!!