Date Incorporation Frustration (sorry for the re-post)

[Danny Boy]

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

 

[Bob Phillips]

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

 

[Joel]

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

 

[Teethless mama]

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]

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