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.
Hi!
Try one of these:
Enter the criteria in cells:
G1 = 2/15/2006
H1 = 7/8/2006
I1 = 1
=SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)
Or, with the criteria hardcoded in the formula:
=SUMPRODUCT(--(A1:A8gt;=DATE(2006,2,15)),--(A1:A8lt;=DATE(2006,7,8)),--(B1:B8=1),C1:C8)
=SUMPRODUCT(--(A1:A8gt;=--quot;2006/2/15quot;),--(A1:A8lt;=--quot;2006/7/8quot;),--(B1:B8=1),C1:C8)
Biff
quot;jrheinschmquot; gt; wrote in message
news
gt; A B C D E
gt; 1 1/21 1 10
gt; 2 2/15 2 5
gt; 3 3/24 1 10
gt; 4 4/15 2 20
gt; 5 5/18 1 10
gt; 6 6/10 1 10
gt; 7 7/8 1 5
gt; 8 8/12 2 5
gt; 9
gt; Search for sum of column C if column B = 1 between the dates of 2/15 -
gt; 7/8:
gt; =35
gt; Probably another simple solution for this one. Thanks for the help.
Thanks for the help, I wish I would have used discussion groups earlier,
would have saved me much time.
quot;Biffquot; wrote:
gt; Hi!
gt;
gt; Try one of these:
gt;
gt; Enter the criteria in cells:
gt;
gt; G1 = 2/15/2006
gt; H1 = 7/8/2006
gt; I1 = 1
gt;
gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)
gt;
gt; Or, with the criteria hardcoded in the formula:
gt;
gt; =SUMPRODUCT(--(A1:A8gt;=DATE(2006,2,15)),--(A1:A8lt;=DATE(2006,7,8)),--(B1:B8=1),C1:C8)
gt;
gt; =SUMPRODUCT(--(A1:A8gt;=--quot;2006/2/15quot;),--(A1:A8lt;=--quot;2006/7/8quot;),--(B1:B8=1),C1:C8)
gt;
gt; Biff
gt;
gt; quot;jrheinschmquot; gt; wrote in message
gt; news
gt; gt; A B C D E
gt; gt; 1 1/21 1 10
gt; gt; 2 2/15 2 5
gt; gt; 3 3/24 1 10
gt; gt; 4 4/15 2 20
gt; gt; 5 5/18 1 10
gt; gt; 6 6/10 1 10
gt; gt; 7 7/8 1 5
gt; gt; 8 8/12 2 5
gt; gt; 9
gt; gt; Search for sum of column C if column B = 1 between the dates of 2/15 -
gt; gt; 7/8:
gt; gt; =35
gt; gt; Probably another simple solution for this one. Thanks for the help.
gt;
gt;
gt;
What if I want to average C1:C8 ? could you please help with this as well?
quot;Biffquot; wrote:
gt; Hi!
gt;
gt; Try one of these:
gt;
gt; Enter the criteria in cells:
gt;
gt; G1 = 2/15/2006
gt; H1 = 7/8/2006
gt; I1 = 1
gt;
gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)
gt;
gt; Or, with the criteria hardcoded in the formula:
gt;
gt; =SUMPRODUCT(--(A1:A8gt;=DATE(2006,2,15)),--(A1:A8lt;=DATE(2006,7,8)),--(B1:B8=1),C1:C8)
gt;
gt; =SUMPRODUCT(--(A1:A8gt;=--quot;2006/2/15quot;),--(A1:A8lt;=--quot;2006/7/8quot;),--(B1:B8=1),C1:C8)
gt;
gt; Biff
gt;
gt; quot;jrheinschmquot; gt; wrote in message
gt; news
gt; gt; A B C D E
gt; gt; 1 1/21 1 10
gt; gt; 2 2/15 2 5
gt; gt; 3 3/24 1 10
gt; gt; 4 4/15 2 20
gt; gt; 5 5/18 1 10
gt; gt; 6 6/10 1 10
gt; gt; 7 7/8 1 5
gt; gt; 8 8/12 2 5
gt; gt; 9
gt; gt; Search for sum of column C if column B = 1 between the dates of 2/15 -
gt; gt; 7/8:
gt; gt; =35
gt; gt; Probably another simple solution for this one. Thanks for the help.
gt;
gt;
gt;
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
=AVERAGE(IF((A1:A8gt;=G1)*(A1:A8lt;=H1)*(B1:B8=I1),C1: C8))
OR, normally entered (but longer):
=SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)/SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1))
Biff
quot;jrheinschmquot; gt; wrote in message
news
gt; What if I want to average C1:C8 ? could you please help with this as well?
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Try one of these:
gt;gt;
gt;gt; Enter the criteria in cells:
gt;gt;
gt;gt; G1 = 2/15/2006
gt;gt; H1 = 7/8/2006
gt;gt; I1 = 1
gt;gt;
gt;gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)
gt;gt;
gt;gt; Or, with the criteria hardcoded in the formula:
gt;gt;
gt;gt; =SUMPRODUCT(--(A1:A8gt;=DATE(2006,2,15)),--(A1:A8lt;=DATE(2006,7,8)),--(B1:B8=1),C1:C8)
gt;gt;
gt;gt; =SUMPRODUCT(--(A1:A8gt;=--quot;2006/2/15quot;),--(A1:A8lt;=--quot;2006/7/8quot;),--(B1:B8=1),C1:C8)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;jrheinschmquot; gt; wrote in message
gt;gt; news
gt;gt; gt; A B C D E
gt;gt; gt; 1 1/21 1 10
gt;gt; gt; 2 2/15 2 5
gt;gt; gt; 3 3/24 1 10
gt;gt; gt; 4 4/15 2 20
gt;gt; gt; 5 5/18 1 10
gt;gt; gt; 6 6/10 1 10
gt;gt; gt; 7 7/8 1 5
gt;gt; gt; 8 8/12 2 5
gt;gt; gt; 9
gt;gt; gt; Search for sum of column C if column B = 1 between the dates of 2/15 -
gt;gt; gt; 7/8:
gt;gt; gt; =35
gt;gt; gt; Probably another simple solution for this one. Thanks for the help.
gt;gt;
gt;gt;
gt;gt;
This is what I entered and it brings back quot;Valuequot;
=AVERAGE(IF((A2:A213gt;=C224)*(A2:A213lt;=C225)*(W2:W2 13=1),H2:H213))
When I tried the sumproduct/sumproduct it brought quot;1quot; could you please advise?
thanks again for the help
quot;Biffquot; wrote:
gt; Entered as an array using the key combination of CTRL,SHIFT,ENTER:
gt;
gt; =AVERAGE(IF((A1:A8gt;=G1)*(A1:A8lt;=H1)*(B1:B8=I1),C1: C8))
gt;
gt; OR, normally entered (but longer):
gt;
gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)/SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1))
gt;
gt; Biff
gt;
gt; quot;jrheinschmquot; gt; wrote in message
gt; news
gt; gt; What if I want to average C1:C8 ? could you please help with this as well?
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; Try one of these:
gt; gt;gt;
gt; gt;gt; Enter the criteria in cells:
gt; gt;gt;
gt; gt;gt; G1 = 2/15/2006
gt; gt;gt; H1 = 7/8/2006
gt; gt;gt; I1 = 1
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)
gt; gt;gt;
gt; gt;gt; Or, with the criteria hardcoded in the formula:
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=DATE(2006,2,15)),--(A1:A8lt;=DATE(2006,7,8)),--(B1:B8=1),C1:C8)
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=--quot;2006/2/15quot;),--(A1:A8lt;=--quot;2006/7/8quot;),--(B1:B8=1),C1:C8)
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;jrheinschmquot; gt; wrote in message
gt; gt;gt; news
gt; gt;gt; gt; A B C D E
gt; gt;gt; gt; 1 1/21 1 10
gt; gt;gt; gt; 2 2/15 2 5
gt; gt;gt; gt; 3 3/24 1 10
gt; gt;gt; gt; 4 4/15 2 20
gt; gt;gt; gt; 5 5/18 1 10
gt; gt;gt; gt; 6 6/10 1 10
gt; gt;gt; gt; 7 7/8 1 5
gt; gt;gt; gt; 8 8/12 2 5
gt; gt;gt; gt; 9
gt; gt;gt; gt; Search for sum of column C if column B = 1 between the dates of 2/15 -
gt; gt;gt; gt; 7/8:
gt; gt;gt; gt; =35
gt; gt;gt; gt; Probably another simple solution for this one. Thanks for the help.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
I tried
=AVERAGE(IF((A2:A213gt;=C224)*(A2:A213lt;=C225)*(W2:W2 13=1),H2:H213))
it brought back quot;valuequot;
when I tried sumproduct/sumproduct, it brought back 1
quot;Biffquot; wrote:
gt; Entered as an array using the key combination of CTRL,SHIFT,ENTER:
gt;
gt; =AVERAGE(IF((A1:A8gt;=G1)*(A1:A8lt;=H1)*(B1:B8=I1),C1: C8))
gt;
gt; OR, normally entered (but longer):
gt;
gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)/SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1))
gt;
gt; Biff
gt;
gt; quot;jrheinschmquot; gt; wrote in message
gt; news
gt; gt; What if I want to average C1:C8 ? could you please help with this as well?
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; Try one of these:
gt; gt;gt;
gt; gt;gt; Enter the criteria in cells:
gt; gt;gt;
gt; gt;gt; G1 = 2/15/2006
gt; gt;gt; H1 = 7/8/2006
gt; gt;gt; I1 = 1
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)
gt; gt;gt;
gt; gt;gt; Or, with the criteria hardcoded in the formula:
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=DATE(2006,2,15)),--(A1:A8lt;=DATE(2006,7,8)),--(B1:B8=1),C1:C8)
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=--quot;2006/2/15quot;),--(A1:A8lt;=--quot;2006/7/8quot;),--(B1:B8=1),C1:C8)
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;jrheinschmquot; gt; wrote in message
gt; gt;gt; news
gt; gt;gt; gt; A B C D E
gt; gt;gt; gt; 1 1/21 1 10
gt; gt;gt; gt; 2 2/15 2 5
gt; gt;gt; gt; 3 3/24 1 10
gt; gt;gt; gt; 4 4/15 2 20
gt; gt;gt; gt; 5 5/18 1 10
gt; gt;gt; gt; 6 6/10 1 10
gt; gt;gt; gt; 7 7/8 1 5
gt; gt;gt; gt; 8 8/12 2 5
gt; gt;gt; gt; 9
gt; gt;gt; gt; Search for sum of column C if column B = 1 between the dates of 2/15 -
gt; gt;gt; gt; 7/8:
gt; gt;gt; gt; =35
gt; gt;gt; gt; Probably another simple solution for this one. Thanks for the help.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
gt;I tried
gt; =AVERAGE(IF((A2:A213gt;=C224)*(A2:A213lt;=C225)*(W2:W2 13=1),H2:H213))
gt; it brought back quot;valuequot;
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 quot;not workingquot; ?????
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
quot;jrheinschmquot; gt; wrote in message
...
gt;I tried
gt; =AVERAGE(IF((A2:A213gt;=C224)*(A2:A213lt;=C225)*(W2:W2 13=1),H2:H213))
gt; it brought back quot;valuequot;
gt; when I tried sumproduct/sumproduct, it brought back 1
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Entered as an array using the key combination of CTRL,SHIFT,ENTER:
gt;gt;
gt;gt; =AVERAGE(IF((A1:A8gt;=G1)*(A1:A8lt;=H1)*(B1:B8=I1),C1: C8))
gt;gt;
gt;gt; OR, normally entered (but longer):
gt;gt;
gt;gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)/SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;jrheinschmquot; gt; wrote in message
gt;gt; news
gt;gt; gt; What if I want to average C1:C8 ? could you please help with this as
gt;gt; gt; well?
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi!
gt;gt; gt;gt;
gt;gt; gt;gt; Try one of these:
gt;gt; gt;gt;
gt;gt; gt;gt; Enter the criteria in cells:
gt;gt; gt;gt;
gt;gt; gt;gt; G1 = 2/15/2006
gt;gt; gt;gt; H1 = 7/8/2006
gt;gt; gt;gt; I1 = 1
gt;gt; gt;gt;
gt;gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)
gt;gt; gt;gt;
gt;gt; gt;gt; Or, with the criteria hardcoded in the formula:
gt;gt; gt;gt;
gt;gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=DATE(2006,2,15)),--(A1:A8lt;=DATE(2006,7,8)),--(B1:B8=1),C1:C8)
gt;gt; gt;gt;
gt;gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=--quot;2006/2/15quot;),--(A1:A8lt;=--quot;2006/7/8quot;),--(B1:B8=1),C1:C8)
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; quot;jrheinschmquot; gt; wrote in message
gt;gt; gt;gt; news
gt;gt; gt;gt; gt; A B C D E
gt;gt; gt;gt; gt; 1 1/21 1 10
gt;gt; gt;gt; gt; 2 2/15 2 5
gt;gt; gt;gt; gt; 3 3/24 1 10
gt;gt; gt;gt; gt; 4 4/15 2 20
gt;gt; gt;gt; gt; 5 5/18 1 10
gt;gt; gt;gt; gt; 6 6/10 1 10
gt;gt; gt;gt; gt; 7 7/8 1 5
gt;gt; gt;gt; gt; 8 8/12 2 5
gt;gt; gt;gt; gt; 9
gt;gt; gt;gt; gt; Search for sum of column C if column B = 1 between the dates of
gt;gt; gt;gt; gt; 2/15 -
gt;gt; gt;gt; gt; 7/8:
gt;gt; gt;gt; gt; =35
gt;gt; gt;gt; gt; Probably another simple solution for this one. Thanks for the help.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
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.quot;Biffquot; wrote:
gt; gt;I tried
gt; gt; =AVERAGE(IF((A2:A213gt;=C224)*(A2:A213lt;=C225)*(W2:W2 13=1),H2:H213))
gt; gt; it brought back quot;valuequot;
gt;
gt; That is an array formula. For it to work properly you MUST use the key
gt; combination of CTRL,SHIFT,ENTER, not just ENTER.
gt;
gt; Hold down both the CTRL key and the SHIFT key then hit ENTER. If done
gt; properly Excel will enclose the formula in squiggly braces { }. You cannot
gt; just type these braces in. You MUST use the key combo.
gt;
gt; As far as the Sumproduct quot;not workingquot; ?????
gt;
gt; Check your data. Make sure the dates are really true Excel dates. Make sure
gt; the data to be summed is really numeric numbers and not TEXT numbers. Hard
gt; to say why you're not getting the correct result.
gt;
gt; Biff
gt;
gt; quot;jrheinschmquot; gt; wrote in message
gt; ...
gt; gt;I tried
gt; gt; =AVERAGE(IF((A2:A213gt;=C224)*(A2:A213lt;=C225)*(W2:W2 13=1),H2:H213))
gt; gt; it brought back quot;valuequot;
gt; gt; when I tried sumproduct/sumproduct, it brought back 1
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Entered as an array using the key combination of CTRL,SHIFT,ENTER:
gt; gt;gt;
gt; gt;gt; =AVERAGE(IF((A1:A8gt;=G1)*(A1:A8lt;=H1)*(B1:B8=I1),C1: C8))
gt; gt;gt;
gt; gt;gt; OR, normally entered (but longer):
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)/SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1))
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;jrheinschmquot; gt; wrote in message
gt; gt;gt; news
gt; gt;gt; gt; What if I want to average C1:C8 ? could you please help with this as
gt; gt;gt; gt; well?
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Biffquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; Hi!
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Try one of these:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Enter the criteria in cells:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; G1 = 2/15/2006
gt; gt;gt; gt;gt; H1 = 7/8/2006
gt; gt;gt; gt;gt; I1 = 1
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=G1),--(A1:A8lt;=H1),--(B1:B8=I1),C1:C8)
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Or, with the criteria hardcoded in the formula:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=DATE(2006,2,15)),--(A1:A8lt;=DATE(2006,7,8)),--(B1:B8=1),C1:C8)
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; =SUMPRODUCT(--(A1:A8gt;=--quot;2006/2/15quot;),--(A1:A8lt;=--quot;2006/7/8quot;),--(B1:B8=1),C1:C8)
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Biff
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;jrheinschmquot; gt; wrote in message
gt; gt;gt; gt;gt; news
gt; gt;gt; gt;gt; gt; A B C D E
gt; gt;gt; gt;gt; gt; 1 1/21 1 10
gt; gt;gt; gt;gt; gt; 2 2/15 2 5
gt; gt;gt; gt;gt; gt; 3 3/24 1 10
gt; gt;gt; gt;gt; gt; 4 4/15 2 20
gt; gt;gt; gt;gt; gt; 5 5/18 1 10
gt; gt;gt; gt;gt; gt; 6 6/10 1 10
gt; gt;gt; gt;gt; gt; 7 7/8 1 5
gt; gt;gt; gt;gt; gt; 8 8/12 2 5
gt; gt;gt; gt;gt; gt; 9
gt; gt;gt; gt;gt; gt; Search for sum of column C if column B = 1 between the dates of
gt; gt;gt; gt;gt; gt; 2/15 -
gt; gt;gt; gt;gt; gt; 7/8:
gt; gt;gt; gt;gt; gt; =35
gt; gt;gt; gt;gt; gt; Probably another simple solution for this one. Thanks for the help.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
留言列表
