sumproduct combine with average formula
 

hello to all excel expert
i want to all "D" column and get the average

criteria is in column A must be ="X"
B =10/01/05 and <=10/31/05
C <0

A B C D


X 10/01/05 0 1
X 10/01/05 0.012 6
X 10/01/05 0.012 2
o 10/01/05 0.012 1
X 11/01/05 0.012 1


--
xtrmhyper
------------------------------------------------------------------------
xtrmhyper's Profile: http://www.excelforum.com/member.php...o&userid=23851
View this thread: http://www.excelforum.com/showthread...hreadid=479013

sumproduct combine with average formula
 

Try this formula

=AVERAGE(IF((A1:A5="X")*(B1:B5=--"2005-10-01")*(B1:B5<=--"2005-10-31")*(C1:
C5<0),D1:D5))

or even

=AVERAGE(IF((A1:A5="X")*(MONTH(B1:B5)=10)*(C1:C5< 0),D1:D5))

which are array formulae, so commit with Ctrl-Shift-Enter

--

HTH

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


"xtrmhyper" wrote
in message ...

hello to all excel expert
i want to all "D" column and get the average

criteria is in column A must be ="X"
B =10/01/05 and <=10/31/05
C <0

A B C D


X 10/01/05 0 1
X 10/01/05 0.012 6
X 10/01/05 0.012 2
o 10/01/05 0.012 1
X 11/01/05 0.012 1


--
xtrmhyper
------------------------------------------------------------------------
xtrmhyper's Profile:
http://www.excelforum.com/member.php...o&userid=23851
View this thread: http://www.excelforum.com/showthread...hreadid=479013