SUMPRODUCT by Date (numeric) value

[Guest]

I am currently working on the following document excerpt

Worklist Received Reported Days to Report
AUT 5/16/2005 6/7/2005 22
AUT 6/28/2005 6/28/05
BM 1/3/2005 1/4/2005 1
BM 1/3/2005 1/6/2005 3

I am trying to find out the number of "worklist" items per month (Received)
and then divide the number of "Days to Report" by that number to get the
average days per item. The following calculation I have is not working on
"numeric values"

my
formula:=SUMPRODUCT(--(IndexData!$G$5:$G$23330>=1/1/2005),--(IndexData!$G$5:$G$23330<=1/31/2005),--(IndexData!$A$5:$A$23330="BM"))

 

[Aladin Akyurek]

What is the expected result given the sample you provided?

 

[Guest]

I expected to get back an answer similar to the following if the formulas
were in each cell
AUT BM Auth Avg Day BM Avg Day
January 2 2
February
March
April
May 1 22
June 1

 

[Bob Phillips]

=AVERAGE(IF((TEXT(IndexData!$G$5:$G$23330,"yyyymm")="200501")*(IndexData!$A$
5:$A$23330="BM"),IndexData!$I$5:$I$23330))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you, the formula works perfectly

=AVERAGE(IF((TEXT(IndexData!$G$5:$G$23330,"yyyymm")="200501")*(IndexData!$A$
5:$A$23330="BM"),IndexData!$I$5:$I$23330))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)