find sum if one col = ? within specific date range

J

jrheinschm

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.
 
B

Biff

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
 
J

jrheinschm

Thanks for the help, I wish I would have used discussion groups earlier,
would have saved me much time.
 
B

Biff

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
 
J

jrheinschm

This is what I entered and it brings back "Value"
=AVERAGE(IF((A2:A213>=C224)*(A2:A213<=C225)*(W2:W213=1),H2:H213))
When I tried the sumproduct/sumproduct it brought "1" could you please advise?
thanks again for the help
 
J

jrheinschm

I tried
=AVERAGE(IF((A2:A213>=C224)*(A2:A213<=C225)*(W2:W213=1),H2:H213))
it brought back "value"
when I tried sumproduct/sumproduct, it brought back 1
 
B

Biff

I tried
=AVERAGE(IF((A2:A213>=C224)*(A2:A213<=C225)*(W2:W213=1),H2:H213))
it brought back "value"
Click to expand...

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
 
H

Hanr3

I cannot get your formula to work either. I have tried it all ways you
listed. I keep getting errors.
 
Top