I have two columns: A is Date (format 1-Jan, 14-Feb, 6-Mar, etc.) and B is
Amount ($). I want to search column A and if it contains Jan, I want to sum
the amounts in Column B. How do I accomplish this?

On Sun, 1 Mar 2009 18:27:01 -0800, MiJa wrote:

I have two columns: A is Date (format 1-Jan, 14-Feb, 6-Mar, etc.) and B is
Amount ($). I want to search column A and if it contains Jan, I want to sum
the amounts in Column B. How do I accomplish this?

Do you want to sum ALL January dates, or only those for a specified year?

For just the January's of 2009, something like:

=sumif(a:a,"="&date(2009,1,1),b:b) - sumif(a:a,""&date(2009,1,31),b:b)

--ron

Hi,

Try:

=SUMPRODUCT(--(A2:A18=D1),--(A2:A18<=E1),B2:B18)

Where D1 contains 1/1/2009 and E1 contains 1/31/2009

In 2007 you can also use:

=SUMIFS(B2:B18,A2:A18,"="&D1,A2:A18,"<="&E1)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"MiJa" wrote:

I have two columns: A is Date (format 1-Jan, 14-Feb, 6-Mar, etc.) and B is
Amount ($). I want to search column A and if it contains Jan, I want to sum
the amounts in Column B. How do I accomplish this?

I tried to create a hybrid of the 2 answers to my question because I do not
want to have additional cells for "1/1/2009" and "1/31/2009;" I need to do
this for each month so I want to incorporate it into the equation. My
equation is
=SUMPRODUCT(--(O19:O999=DATE(2009,1,1)),--(O19:O999<=DATE(2009,1,31)),Q19:Q999) but it just returns 0.00. Any suggestions?

To expand on my question, if I have 4 Jan dates in Column A I want to sum
the 4 corresponding amounts in Column B. The answer from Ron summed the whole
column B.

"Shane Devenshire" wrote:

Hi,

Try:

=SUMPRODUCT(--(A2:A18=D1),--(A2:A18<=E1),B2:B18)

Where D1 contains 1/1/2009 and E1 contains 1/31/2009

In 2007 you can also use:

=SUMIFS(B2:B18,A2:A18,"="&D1,A2:A18,"<="&E1)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"MiJa" wrote:

I have two columns: A is Date (format 1-Jan, 14-Feb, 6-Mar, etc.) and B is
Amount ($). I want to search column A and if it contains Jan, I want to sum
the amounts in Column B. How do I accomplish this?

On Mon, 2 Mar 2009 07:53:04 -0800, MiJa wrote:

I tried to create a hybrid of the 2 answers to my question because I do not
want to have additional cells for "1/1/2009" and "1/31/2009;" I need to do
this for each month so I want to incorporate it into the equation. My
equation is
=SUMPRODUCT(--(O19:O999=DATE(2009,1,1)),--(O19:O999<=DATE(2009,1,31)),Q19:Q999) but it just returns 0.00. Any suggestions?

If your data is in columns A & B, you didn't change the references.


To expand on my question, if I have 4 Jan dates in Column A I want to sum
the 4 corresponding amounts in Column B. The answer from Ron summed the whole
column B.

If my answer

=SUMIF(A:A,"="&DATE(2009,1,1),B:B) - SUMIF(A:A,""&DATE(2009,1,31),B:B)

summed the entire column B, and not just the January entries, then either you
did not enter it correctly, or there is something about your data that you have
not told us.

--ron