Summing values in a column if the date in another is of a certain month

[Harvey Coward]

Hi,

I hope that this should be quite easy, but I'll be blowed if I can find
an answer on google...


Column F has dates, some rows may be blank

Column G has currency values, some rows may be blank but if a row in
column F has a value, so will the corresponding row in column G

And what I would like to do is produce on another sheet, the total per
month of each year (as set by column F) of the values in column G.

i.e.

December 2006 1234
January 2007 5678

Any help appreciated.

 

[Bob Phillips]

=SUMPRODUCT(--(YEAR(Sheet1!$F$2:$F$200)=YEAR(A1)),--(MONTH(Sheet1!$F$2:$F$200)=MONTH(A1)),Sheet1!$G$2:$G$200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Stephen]

Hi,

I hope that this should be quite easy, but I'll be blowed if I can find an
answer on google...


Column F has dates, some rows may be blank

Column G has currency values, some rows may be blank but if a row in
column F has a value, so will the corresponding row in column G

And what I would like to do is produce on another sheet, the total per
month of each year (as set by column F) of the values in column G.

i.e.

December 2006 1234
January 2007 5678

Any help appreciated.

=SUMPRODUCT(--(MONTH(F1:F999)=12),--(YEAR(F1:F999=2006)),G1:G999)
will give you the total for Dec 2006. (Obviously change the ranges to suit
your data.)

You can replace the 12 and 2006 in this formula by appropriate cell
references or formulas.

On the "answer" sheet, I would suggest entering in column A a list of dates,
each being (say) the first of the month (such as 1-Dec-06, 1-Jan-07). You
can format these to display "month and date" if you wish. The formula (in
B1, copied down as far as required) would then become something like:
=SUMPRODUCT(--(Sheet1!MONTH(F1:F999)=MONTH(A1)),--(Sheet1!YEAR(F1:F999)=YEAR(A1)),Sheet1!G1:G999)

 

[Harvey Coward]

Thanks, this works a treat.

=SUMPRODUCT(--(YEAR(Sheet1!$F$2:$F$200)=YEAR(A1)),--(MONTH(Sheet1!$F$2:$
F$200)=MONTH(A1)),Sheet1!$G$2:$G$200)