IF Function Question

[MiJa]

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?

 

[Ron Rosenfeld]

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

 

[Shane Devenshire]

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)

 

[MiJa]

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.

 

[Ron Rosenfeld]

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