SUMPRODUCT by Date (numeric) value
 

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"))

What is the expected result given the sample you provided?

Jeanette wrote:
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"))

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


"Aladin Akyurek" wrote:

What is the expected result given the sample you provided?

Jeanette wrote:
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"))

=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)


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


"Aladin Akyurek" wrote:

What is the expected result given the sample you provided?

Jeanette wrote:
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"))

Thank you, the formula works perfectly

"Bob Phillips" wrote:

=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)


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


"Aladin Akyurek" wrote:

What is the expected result given the sample you provided?

Jeanette wrote:
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"))