Just for clarification, here is an example of where I was able to incorporate
"date parameters" and the formula worked just as I wanted it to. And yes I am
using Cell A4 to reflect the date, and I am using Excel 2007. The formula
below works perfectly:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*(('Raw Data'!$H$4:$H$5000="Barb
B")*(('Raw Data'!$D$4:$D$5000="LATE")))))

However, when I tried to do the exact same thing in my other two formulas
(see example below), I kept getting error messages:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw Data'!$H$4:$H$5000,"Barb
B"),AVERAGE(IF('Raw Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw
Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw Data'!$F$4:$F$5000="OP",'Raw
Data'!$C$4:$C$5000)),"")

Again thank you for any help.....So far the suggestions don't seem to be
generating outcomes without errors.

Dan

You cannot do that as the embedded average will return a single value which
will throw SP as it wants a same size array.

What are you trying to do exactly, get an average instead of a sum? If so,
maybe this array formula

=AVERAGE(IF(
('Raw Data'!$A$4:$A$5000=--"2009,-07-01")
*('Raw Data'!$A$4:$A$5000<=--"2009-09-30")
*('Raw Data'!$H$4:$H$5000="Barb B"),'Raw Data'!$C$4:$C$5000))


--
__________________________________
HTH

Bob

"Danny Boy" wrote in message
...
Just for clarification, here is an example of where I was able to
incorporate
"date parameters" and the formula worked just as I wanted it to. And yes I
am
using Cell A4 to reflect the date, and I am using Excel 2007. The formula
below works perfectly:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*(('Raw Data'!$H$4:$H$5000="Barb
B")*(('Raw Data'!$D$4:$D$5000="LATE")))))

However, when I tried to do the exact same thing in my other two formulas
(see example below), I kept getting error messages:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw Data'!$H$4:$H$5000,"Barb
B"),AVERAGE(IF('Raw Data'!$H$4:$H$5000="Barb B",'Raw
Data'!$C$4:$C$5000)),"")

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw
Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw Data'!$F$4:$F$5000="OP",'Raw
Data'!$C$4:$C$5000)),"")

Again thank you for any help.....So far the suggestions don't seem to be
generating outcomes without errors.

Dan

First, the IF is wrong. the parethesis is in the wrong place

From
IF('Raw Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")

to
IF('Raw Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000,"")))

Second it doesn't make sense to perrform sumproduct on a blank character if
the IF statement is false.

Should be this
('Raw Data'!$H$4:$H$5000="Barb B")*('Raw Data'!$C$4:$C$5000)


Now does your formula makes any sense. Suppose you had all the items meet
your date requirements and equal Barb B

then you would have the following :

=sumproduct('Raw Data'!$H$4:$H$5000,average('Raw Data'!$C$4:$C$5000))











"Danny Boy" wrote:

Just for clarification, here is an example of where I was able to incorporate
"date parameters" and the formula worked just as I wanted it to. And yes I am
using Cell A4 to reflect the date, and I am using Excel 2007. The formula
below works perfectly:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*(('Raw Data'!$H$4:$H$5000="Barb
B")*(('Raw Data'!$D$4:$D$5000="LATE")))))

However, when I tried to do the exact same thing in my other two formulas
(see example below), I kept getting error messages:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw Data'!$H$4:$H$5000,"Barb
B"),AVERAGE(IF('Raw Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw
Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw Data'!$F$4:$F$5000="OP",'Raw
Data'!$C$4:$C$5000)),"")

Again thank you for any help.....So far the suggestions don't seem to be
generating outcomes without errors.

Dan

I'm not sure what are you trying to do.

using Cell A4 to reflect the date, and I am using Excel 2007

Instead of using sumproduct and average functions you could use AVERAGEIFS



"Danny Boy" wrote:

Just for clarification, here is an example of where I was able to incorporate
"date parameters" and the formula worked just as I wanted it to. And yes I am
using Cell A4 to reflect the date, and I am using Excel 2007. The formula
below works perfectly:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*(('Raw Data'!$H$4:$H$5000="Barb
B")*(('Raw Data'!$D$4:$D$5000="LATE")))))

However, when I tried to do the exact same thing in my other two formulas
(see example below), I kept getting error messages:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw Data'!$H$4:$H$5000,"Barb
B"),AVERAGE(IF('Raw Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw
Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw Data'!$F$4:$F$5000="OP",'Raw
Data'!$C$4:$C$5000)),"")

Again thank you for any help.....So far the suggestions don't seem to be
generating outcomes without errors.

Dan

So after reading, and editing all the various feedback the formula below is
what I came up with. When entered as an array it seems to work PERFECTLY, and
I tested it several times. If anyone has any final feedback on what I wrote
please let me know (I always appreciate feedback). It still seems a bit
sloppy, but unlike what I started with, this does work to provide me with the
average days it takes for Barb B to close out her files (for all files turned
in between July 1, 2009-September 30, 2009):

=IF(COUNTIF('Raw Data'!$H$4:$H$5000,"Barb B"),SUMPRODUCT(('Raw
Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)))*AVERAGE(IF(' Raw
Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")

"Danny Boy" wrote:

Just for clarification, here is an example of where I was able to incorporate
"date parameters" and the formula worked just as I wanted it to. And yes I am
using Cell A4 to reflect the date, and I am using Excel 2007. The formula
below works perfectly:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*(('Raw Data'!$H$4:$H$5000="Barb
B")*(('Raw Data'!$D$4:$D$5000="LATE")))))

However, when I tried to do the exact same thing in my other two formulas
(see example below), I kept getting error messages:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw Data'!$H$4:$H$5000,"Barb
B"),AVERAGE(IF('Raw Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw
Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw Data'!$F$4:$F$5000="OP",'Raw
Data'!$C$4:$C$5000)),"")

Again thank you for any help.....So far the suggestions don't seem to be
generating outcomes without errors.

Dan