| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sum a column with only a certain date range?
I am trying to sum a column with only certain dates, mean if the date in
column G is Jan-07, I want to sum column B. .....A..........B................C...............D ..............E...............F.............G..... ...........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Maybe this:-
=SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
is this helping you ?
=SUMIF(G:G;"jan-07";B:B) "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Mike,
I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
I think Mike meant:
=SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Let me apologize for I am extremely confused. I only have two columns that
I'd like to work ith G has the month and year, B has the number that I'd like to sum. Column C doesn't have anything to do with equation I'm looking for. I hope that I'm just misunderstanding. Thanks again. -- DMM "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
I did, thanks Stephen
"Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Hi,
You have 2 choices, embed the date in the formula or as in the offered solutions put the date to sume in another cell which in this case is C1. Best practice (some may argue otherwise) is to put the date in a cell because this saves editing formula and lessens the risk of error. So the solution to your summing problem is =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) Wher C1 contains a date (say) 1/1/2007. The formula will then sum everything for January 2007. C1 can be any cell on your sheet. Mike "soconfused" wrote: Let me apologize for I am extremely confused. I only have two columns that I'd like to work ith G has the month and year, B has the number that I'd like to sum. Column C doesn't have anything to do with equation I'm looking for. I hope that I'm just misunderstanding. Thanks again. -- DMM "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Okay, I have tried that and I am coming up with 0 for an answer which is
incorrect. -- DMM "Mike H" wrote: Hi, You have 2 choices, embed the date in the formula or as in the offered solutions put the date to sume in another cell which in this case is C1. Best practice (some may argue otherwise) is to put the date in a cell because this saves editing formula and lessens the risk of error. So the solution to your summing problem is =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) Wher C1 contains a date (say) 1/1/2007. The formula will then sum everything for January 2007. C1 can be any cell on your sheet. Mike "soconfused" wrote: Let me apologize for I am extremely confused. I only have two columns that I'd like to work ith G has the month and year, B has the number that I'd like to sum. Column C doesn't have anything to do with equation I'm looking for. I hope that I'm just misunderstanding. Thanks again. -- DMM "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
92203 1 400 330.9 313.0 107.9 Jan-07 376.0
92221 1 192 191.3 186.2 11.3 Jan-07 111.0 92214 1 917 918.6 941.8 4.5 Feb-07 313.0 92217 1 210 214.0 263.4 12.0 Feb-07 107.0 92218 1 210 211.0 207.8 0.0 Feb-07 107.0 92232 1 372 371.0 359.7 5.2 Feb-07 75.0 92254 1 45 44.7 50.2 0.0 Feb-07 13.0 92264 1 85 85.8 82.6 0.0 Feb-07 15.0 92181 1 300 305.0 335.9 28.0 Mar-07 0.0 92184 1 300 305.0 335.9 28.0 Mar-07 0.0 92187 1 300 305.0 335.9 28.0 Mar-07 0.0 92190 1 300 305.0 335.9 28.0 Mar-07 0.0 92193 1 300 305.0 335.9 28.0 Mar-07 0.0 92244 1 175 150.5 156.9 4.2 Mar-07 62.0 92224 2 42 38.0 31.0 5.5 Mar-07 0.0 92225 3 63 57.0 42.5 0.0 Mar-07 0.0 92230 2 767 655.9 743.9 98.5 Mar-07 300.0 92265 1 0 0.0 0.0 0.0 Mar-07 0.0 92174 1 779 565.2 610.5 89.7 Apr-07 919.0 92175 2 738 1086.7 1097.4 318.4 Apr-07 540.0 92182 1 60 61.0 44.8 19.5 Apr-07 0.0 92185 1 60 61.0 44.8 0.0 Apr-07 0.0 92188 1 60 61.0 28.0 0.0 Apr-07 0.0 92194 1 60 61.0 40.0 0.0 Apr-07 0.0 92226 4 84 76.0 43.0 0.0 Apr-07 0.0 92227 2 42 38.0 24.0 0.0 Apr-07 0.0 92231 1 360 328.6 315.6 128.9 Apr-07 542.0 92234 1 460 454.8 533.4 30.7 Apr-07 183.0 The abve is a copy of the data that I am using. I already have the dates in a certain column and I am trying to avoid putting information in another column that has to be changed on a regular basis. I apologize, I did the formula wrong the first time, it did you as you said it would, but I would rather have a formula where I can use the data already stored in he worksheet. Thanks again. -- DMM "Mike H" wrote: I did, thanks Stephen "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
A likely reason is that your dates aren't actually dates,
In column G re-type one of your dates to ensure it actually is a date and see if the formula sums that row. Mike "soconfused" wrote: Okay, I have tried that and I am coming up with 0 for an answer which is incorrect. -- DMM "Mike H" wrote: Hi, You have 2 choices, embed the date in the formula or as in the offered solutions put the date to sume in another cell which in this case is C1. Best practice (some may argue otherwise) is to put the date in a cell because this saves editing formula and lessens the risk of error. So the solution to your summing problem is =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) Wher C1 contains a date (say) 1/1/2007. The formula will then sum everything for January 2007. C1 can be any cell on your sheet. Mike "soconfused" wrote: Let me apologize for I am extremely confused. I only have two columns that I'd like to work ith G has the month and year, B has the number that I'd like to sum. Column C doesn't have anything to do with equation I'm looking for. I hope that I'm just misunderstanding. Thanks again. -- DMM "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Maybe
=SUMPRODUCT(--(YEAR(A1:A10)=2007),--(MONTH(A1:A10)=1),B1:B10) Mike "soconfused" wrote: 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 92214 1 917 918.6 941.8 4.5 Feb-07 313.0 92217 1 210 214.0 263.4 12.0 Feb-07 107.0 92218 1 210 211.0 207.8 0.0 Feb-07 107.0 92232 1 372 371.0 359.7 5.2 Feb-07 75.0 92254 1 45 44.7 50.2 0.0 Feb-07 13.0 92264 1 85 85.8 82.6 0.0 Feb-07 15.0 92181 1 300 305.0 335.9 28.0 Mar-07 0.0 92184 1 300 305.0 335.9 28.0 Mar-07 0.0 92187 1 300 305.0 335.9 28.0 Mar-07 0.0 92190 1 300 305.0 335.9 28.0 Mar-07 0.0 92193 1 300 305.0 335.9 28.0 Mar-07 0.0 92244 1 175 150.5 156.9 4.2 Mar-07 62.0 92224 2 42 38.0 31.0 5.5 Mar-07 0.0 92225 3 63 57.0 42.5 0.0 Mar-07 0.0 92230 2 767 655.9 743.9 98.5 Mar-07 300.0 92265 1 0 0.0 0.0 0.0 Mar-07 0.0 92174 1 779 565.2 610.5 89.7 Apr-07 919.0 92175 2 738 1086.7 1097.4 318.4 Apr-07 540.0 92182 1 60 61.0 44.8 19.5 Apr-07 0.0 92185 1 60 61.0 44.8 0.0 Apr-07 0.0 92188 1 60 61.0 28.0 0.0 Apr-07 0.0 92194 1 60 61.0 40.0 0.0 Apr-07 0.0 92226 4 84 76.0 43.0 0.0 Apr-07 0.0 92227 2 42 38.0 24.0 0.0 Apr-07 0.0 92231 1 360 328.6 315.6 128.9 Apr-07 542.0 92234 1 460 454.8 533.4 30.7 Apr-07 183.0 The abve is a copy of the data that I am using. I already have the dates in a certain column and I am trying to avoid putting information in another column that has to be changed on a regular basis. I apologize, I did the formula wrong the first time, it did you as you said it would, but I would rather have a formula where I can use the data already stored in he worksheet. Thanks again. -- DMM "Mike H" wrote: I did, thanks Stephen "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Mike,
Thanks for being so patient with me, that formula worked exactly like I wanted it to. -- DMM "Mike H" wrote: I did, thanks Stephen "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Splendid news and thanks for the feedback
"soconfused" wrote: Mike, Thanks for being so patient with me, that formula worked exactly like I wanted it to. -- DMM "Mike H" wrote: I did, thanks Stephen "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum a column with only a certain date range?
Hi
Another alternative would be =SUMPRODUCT(--(TEXT(G1:G10,"yymm")=TEXT(C1,"yymm")),B1:B10) -- Regards Roger Govier "soconfused" wrote in message ... Mike, Thanks for being so patient with me, that formula worked exactly like I wanted it to. -- DMM "Mike H" wrote: I did, thanks Stephen "Stephen" wrote: I think Mike meant: =SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10) "soconfused" wrote in message ... Mike, I already have the date in column G which is Ja-07 and the number is column B which is 1. I guess don't undertand your answer. Can you explain further?? -- DMM "Mike H" wrote: Maybe this:- =SUMPRODUCT(--(YEAR(A1:A10)=YEAR(C1)),--(MONTH(A1:A10)=MONTH(C1)),B1:B10) With a date in C1 which contains the month and year to sum. Mike "soconfused" wrote: I am trying to sum a column with only certain dates, mean if the date in column G is Jan-07, I want to sum column B. ....A..........B................C...............D. .............E...............F.............G...... ..........H... 92203 1 400 330.9 313.0 107.9 Jan-07 376.0 92221 1 192 191.3 186.2 11.3 Jan-07 111.0 Thanks in advance for any information that could help me. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
| Sum Column by Date Range | Excel Discussion (Misc queries) | |||
| select date range in column | Excel Worksheet Functions | |||
| 2 column lookup - match to date range | Excel Worksheet Functions | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Linear Mode
