I cannot get your formula to work either. I have tried it all ways you
listed. I keep getting errors.
--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.


"Biff" wrote:

I tried
=AVERAGE(IF((A2:A213=C224)*(A2:A213<=C225)*(W2:W2 13=1),H2:H213))
it brought back "value"

That is an array formula. For it to work properly you MUST use the key
combination of CTRL,SHIFT,ENTER, not just ENTER.

Hold down both the CTRL key and the SHIFT key then hit ENTER. If done
properly Excel will enclose the formula in squiggly braces { }. You cannot
just type these braces in. You MUST use the key combo.

As far as the Sumproduct "not working" ?????

Check your data. Make sure the dates are really true Excel dates. Make sure
the data to be summed is really numeric numbers and not TEXT numbers. Hard
to say why you're not getting the correct result.

Biff

"jrheinschm" wrote in message
...
I tried
=AVERAGE(IF((A2:A213=C224)*(A2:A213<=C225)*(W2:W2 13=1),H2:H213))
it brought back "value"
when I tried sumproduct/sumproduct, it brought back 1

"Biff" wrote:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=AVERAGE(IF((A1:A8=G1)*(A1:A8<=H1)*(B1:B8=I1),C1: C8))

OR, normally entered (but longer):

=SUMPRODUCT(--(A1:A8=G1),--(A1:A8<=H1),--(B1:B8=I1),C1:C8)/SUMPRODUCT(--(A1:A8=G1),--(A1:A8<=H1),--(B1:B8=I1))

Biff

"jrheinschm" wrote in message
...
What if I want to average C1:C8 ? could you please help with this as
well?

"Biff" wrote:

Hi!

Try one of these:

Enter the criteria in cells:

G1 = 2/15/2006
H1 = 7/8/2006
I1 = 1

=SUMPRODUCT(--(A1:A8=G1),--(A1:A8<=H1),--(B1:B8=I1),C1:C8)

Or, with the criteria hardcoded in the formula:

=SUMPRODUCT(--(A1:A8=DATE(2006,2,15)),--(A1:A8<=DATE(2006,7,8)),--(B1:B8=1),C1:C8)

=SUMPRODUCT(--(A1:A8=--"2006/2/15"),--(A1:A8<=--"2006/7/8"),--(B1:B8=1),C1:C8)

Biff

"jrheinschm" wrote in message
...
A B C D E
1 1/21 1 10
2 2/15 2 5
3 3/24 1 10
4 4/15 2 20
5 5/18 1 10
6 6/10 1 10
7 7/8 1 5
8 8/12 2 5
9
Search for sum of column C if column B = 1 between the dates of
2/15 -
7/8:
=35
Probably another simple solution for this one. Thanks for the help.