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

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.

--
Best regards
Harvey Coward

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

=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)



"Harvey Coward" wrote in message
...
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.

--
Best regards
Harvey Coward

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

"Harvey Coward" wrote in message
...
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.

--
Best regards
Harvey Coward


=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)

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

Thanks, this works a treat.


In message , Bob Phillips
writes


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


--
Best regards
Harvey Coward