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.

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.

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.

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.

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.