Always with the SUMPRODUCT questions!

[MarianneR]

Hi. Sorry that I am so slow to learn; but thank you in advance for al
of your help.

I'm trying to do a sumproduct for more than two criteria. Here's wha
my sheet might look like:

ID.........Diagnosis........Review........MonthReview.....TOTALpresent.....TOTALpossible
1...........abc.................1st..............Mar-04.................14.......................21
2...........xyz.................1st...............Apr-04.................7........................16
3...........lmn.................2nd..............Mar-04................20......................21
4...........xyz.................2nd..............Jun-04.................13......................14

What I want to do is select all Diagnosis ABC, for the 1st review i
March. I'm going to set up a crosstab-type table for the averages
where the cells would be the total present over the total possible.

1st Review (Diagnosis/Month)

..........Mar-04.....Apr-04 (etc.)
ABC...65%.........95%
LMN...21%.........55%
XYZ....35%.........85%


I hope this makes sense.

I tried using the sumproduct function and could not make it work.
Maybe there's an easier way?

Thanks,
Marianne :cool

 

[duane]

i do not understand the calculations which give you the results (65%
95%. etc) you sho

 

[Guest]

I am not totally sure what you are trying to accomplish because 14/21 does
not equal 65% and 20/21 does not equal 21%. Do you know a what pivot table
is? You can use pivot tables to create reports that do calculations for you.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(B1:B100="abc"),--(C1:C100="1st"),--(D1100="Mar-04"),E1
:E100)/SUMPRODUCT(--(B1:B100="abc"),--(C1:C100="1st"),--(D1100="Mar-0
4"),F1:F100)

or if you have real date values just formated as MMM-YY try:
=SUMPRODUCT(--(B1:B100="abc"),--(C1:C100="1st"),--(TEXT(D1100,"MMM-YY
")="Mar-04"),E1:E100)/SUMPRODUCT(--(B1:B100="abc"),--(C1:C100="1st"),--
(TEXT(D1100,"MMM-YY")="Mar-04"),F1:F100)

 

[Max]

One way ..

Assume this table is in Sheet1, cols A to F
data from row2 down

ID.........Diagnosis........Review........MonthReview.....TOTALpresent.....T
OTALpossible
1...........abc.................1st..............Mar-04.................14..
......................21
2...........xyz.................1st...............Apr-04.................7..
.......................16
3...........lmn.................2nd..............Mar-04................20...
....................21

4...........xyz.................2nd..............Jun-04.................13..
.....................14

where col D "MonthReview"
contains dates "1-Mar-04", "1-Apr-04", etc in mmm-yy format
(which'll appear as Mar-04, Apr-04 etc)

and this is in Sheet2

1st....Mar-04.....Apr-04 (etc.)
ABC...65%.........95%
LMN...21%.........55%
XYZ....35%.........85%

with A1 containing: "1st" (i.e. the Review #)
and in B1, C1 ... are dates "1-Mar-04", "1-Apr-04", etc in mmm-yy format
(similar to the dates in Sheet1's col D "MonthReview")

Put in B2:

=IF(ISNA(MATCH(1,(Sheet1!$C$2:$C$10=$A$1)*(Sheet1!$B$2:$B$10=$A2)*(Sheet1!$D
$2:$D$10=B$1),0)),"",INDEX(Sheet1!$E$2:$F$10,MATCH(1,(Sheet1!$C$2:$C$10=$A$1
)*(Sheet1!$B$2:$B$10=$A2)*(Sheet1!$D$2:$D$10=B$1),0),1)/INDEX(Sheet1!$E$2:$F
$10,MATCH(1,(Sheet1!$C$2:$C$10=$A$1)*(Sheet1!$B$2:$B$10=$A2)*(Sheet1!$D$2:$D
$10=B$1),0),2))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Format B2 as percentage, and fill B2 down / across or across / down to
populate the table

Adapt the ranges to suit