caluculate an average based on 2 criteria

[cstrange]

I need to caluculate an average based on 2 criteria. Using the example
below, if A="brian" (F1) and D=not blank, then average all numbers in D
that have F1 ("brian") in column A. So in the example below, it would
average D1 & D4 (not include D2).

HELP anyone! Thanks.

Database (Sheet1)
A B C D*
1 brian 08/01/05 09/01/05 30
2 chris 08/15/05 09/01/05 15
3 pam
4 brian 08/15/05 09/01/05 15
5 brian 08/31/05

*-Formula to calculate days =IF(M2>0,(DAYS360(G2,M2))," ")

Totals/Formula (Sheet2)
F G
1 brian NEW FORMULA WILL GO HERE
2 chris
3 pam

 

[Guest]

Wouldn't it need to average D5 also?

I would suggest a lookup formula.

 

[Peo Sjoblom]

=AVERAGE(IF((A1:A5=F1)*(D15<>""),D15))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Biff]

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(SUMIF(Sheet1!A$1:A$5,F1,Sheet1!D$1$5),AVERAGE(IF(Sheet1!A$1:A$5=F1,IF(Sheet1!D$1$5<>"",Sheet1!D$1$5))),0)

Copy down as needed.

Biff