Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing first 'n' values in a column or row | Excel Worksheet Functions | |||
Summing all numeric values only in cells in a column | Excel Worksheet Functions | |||
summing last values in column | Excel Worksheet Functions | |||
Summing one column if two other columns' values appear in other sh | Excel Worksheet Functions | |||
Summing Values In Current Month Only | Excel Worksheet Functions |