Average data with date and if constraints

J

jrheinschm

=SUMPRODUCT(--(A2:A213>=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPRODUCT(--(A2:A213>=C224),--(A2:A213<=C225),--(W2:W215=1),H2:H213)
This is the suggestion one of you gave me, however it doesn't seem to work.
col A are dates

I've been working on this for hours and can't get it??
Please help
 
D

Don Guillett

Did NOT test. Try this arrary formula which must be entered using
ctrl+shift+enter
=average(if(A2:A213>=C224,A2:A213<=C225,W2:W213=1,H2:H213))
 
J

jrheinschm

when you say enter as array using cntrl, shift ,enter I don't
understand? remember I am A rookie at excel.
thanks so much for the help.
 
D

Don Guillett

when you create or edit a formula you then touch the enter key to leave the
cell. Instead hold the ctrl & shift keys down while you touch the enter key.
 
B

Bob Phillips

That formula will not work, you need

=AVERAGE(IF((A2:A213>=C224)*(A2:A213<=C225)*(W2:W213=1),H2:H213))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top