| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find sum if one col = ? within specific date range
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find sum if one col = ? within specific date range
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find sum if one col = ? within specific date range
Thanks for the help, I wish I would have used discussion groups earlier,
would have saved me much time. "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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find sum if one col = ? within specific date range
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find sum if one col = ? within specific date range
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find sum if one col = ? within specific date range
This is what I entered and it brings back "Value"
=AVERAGE(IF((A2:A213=C224)*(A2:A213<=C225)*(W2:W2 13=1),H2:H213)) When I tried the sumproduct/sumproduct it brought "1" could you please advise? thanks again for the help "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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find sum if one col = ? within specific date range
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find sum if one col = ? within specific date range
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find sum if one col = ? within specific date range
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
| Date Range within one cell | New Users to Excel | |||
| How do I sum a range if the date is is greater than today's date? | Excel Worksheet Functions | |||
| HELP - Date Range In 1 Cell Calculation | Excel Worksheet Functions | |||
| Date range criteria and Pivot tables (again!) | Excel Worksheet Functions | |||
Linear Mode
