How do I sum a column with only a certain date range?

[soconfused]

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.

 

[joel]

is this helping you ?
=SUMIF(G:G;"jan-07";B:B)

 

[Mike H]

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]

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??

 

[Stephen]

I think Mike meant:
=SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10)

 

[soconfused]

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.

 

[Mike H]

I did, thanks Stephen

I think Mike meant:
=SUMPRODUCT(--(YEAR(G1:G10)=YEAR(C1)),--(MONTH(G1:G10)=MONTH(C1)),B1:B10)

 

[Mike H]

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]

Okay, I have tried that and I am coming up with 0 for an answer which is
incorrect.

 

[soconfused]

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.

 

[Mike H]

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

 

[Mike H]

Maybe

=SUMPRODUCT(--(YEAR(A1:A10)=2007),--(MONTH(A1:A10)=1),B1:B10)

Mike

 

[soconfused]

Mike,

Thanks for being so patient with me, that formula worked exactly like I
wanted it to.

 

[Mike H]

Splendid news and thanks for the feedback

Mike,

Thanks for being so patient with me, that formula worked exactly like I
wanted it to.

 

[Roger Govier]

Hi

Another alternative would be
=SUMPRODUCT(--(TEXT(G1:G10,"yymm")=TEXT(C1,"yymm")),B1:B10)