| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating monthly totals
Does anyone know the best formula for adding figures in a column that
correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Hi
To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Thanks Arvi, does SaleDate have to be replaced by the month required or is
that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Hi
SaleDate and Commission are ranges, where sale dates and commission figures are stored. You can use there range references or named ranges. NB. Both ranges SaleDates and Commission MUST be of same dimension. When SalaDate doesn't contain valid format dates, then you have to modify the formula - how, depends on your data then. Arvi Laanemets "Pieman" wrote in message ... Thanks Arvi, does SaleDate have to be replaced by the month required or is that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Avri, thanks for explaining that.
I have entered the following formula for the current year: =SUMPRODUCT("--(MONTH(Websites!B5:B31)=01,--(YEAR(Websites!B5:B31)=YEAR(TODAY()),Websites!R5:R 31)") but I just get #VALUE! in the cell where the monthly commission total should be displayed. Have you any ideas why this is? Thanks Simon "Arvi Laanemets" wrote: Hi SaleDate and Commission are ranges, where sale dates and commission figures are stored. You can use there range references or named ranges. NB. Both ranges SaleDates and Commission MUST be of same dimension. When SalaDate doesn't contain valid format dates, then you have to modify the formula - how, depends on your data then. Arvi Laanemets "Pieman" wrote in message ... Thanks Arvi, does SaleDate have to be replaced by the month required or is that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Hi
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31) Arvi Laanemets "Pieman" wrote in message ... Avri, thanks for explaining that. I have entered the following formula for the current year: =SUMPRODUCT("--(MONTH(Websites!B5:B31)=01,--(YEAR(Websites!B5:B31)=YEAR(TODA Y()),Websites!R5:R31)") but I just get #VALUE! in the cell where the monthly commission total should be displayed. Have you any ideas why this is? Thanks Simon "Arvi Laanemets" wrote: Hi SaleDate and Commission are ranges, where sale dates and commission figures are stored. You can use there range references or named ranges. NB. Both ranges SaleDates and Commission MUST be of same dimension. When SalaDate doesn't contain valid format dates, then you have to modify the formula - how, depends on your data then. Arvi Laanemets "Pieman" wrote in message ... Thanks Arvi, does SaleDate have to be replaced by the month required or is that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Avri
I have entered this formula: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31). But I still get a popup error message that says: 'The formula you typed contains an error'. Thanks Simon "Arvi Laanemets" wrote: Hi =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31) Arvi Laanemets "Pieman" wrote in message ... Avri, thanks for explaining that. I have entered the following formula for the current year: =SUMPRODUCT("--(MONTH(Websites!B5:B31)=01,--(YEAR(Websites!B5:B31)=YEAR(TODA Y()),Websites!R5:R31)") but I just get #VALUE! in the cell where the monthly commission total should be displayed. Have you any ideas why this is? Thanks Simon "Arvi Laanemets" wrote: Hi SaleDate and Commission are ranges, where sale dates and commission figures are stored. You can use there range references or named ranges. NB. Both ranges SaleDates and Commission MUST be of same dimension. When SalaDate doesn't contain valid format dates, then you have to modify the formula - how, depends on your data then. Arvi Laanemets "Pieman" wrote in message ... Thanks Arvi, does SaleDate have to be replaced by the month required or is that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Hi
To locate the part of formula, causing the error, try to calculate different parts of formula =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1)) =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY()))) =SUMPRODUCT(Websites!R5:R31) =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ()))) etc. Arvi Laanemets "Pieman" wrote in message ... Avri I have entered this formula: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31). But I still get a popup error message that says: 'The formula you typed contains an error'. Thanks Simon "Arvi Laanemets" wrote: Hi =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31) Arvi Laanemets "Pieman" wrote in message ... Avri, thanks for explaining that. I have entered the following formula for the current year: =SUMPRODUCT("--(MONTH(Websites!B5:B31)=01,--(YEAR(Websites!B5:B31)=YEAR(TODA Y()),Websites!R5:R31)") but I just get #VALUE! in the cell where the monthly commission total should be displayed. Have you any ideas why this is? Thanks Simon "Arvi Laanemets" wrote: Hi SaleDate and Commission are ranges, where sale dates and commission figures are stored. You can use there range references or named ranges. NB. Both ranges SaleDates and Commission MUST be of same dimension. When SalaDate doesn't contain valid format dates, then you have to modify the formula - how, depends on your data then. Arvi Laanemets "Pieman" wrote in message ... Thanks Arvi, does SaleDate have to be replaced by the month required or is that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Avri
I have tried all the parts of the formula and the only one that causes the error is: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY()))) All these worked fine: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1)) =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY()))) =SUMPRODUCT(Websites!R5:R31) Any idea where the error is located? Many thanks Simon "Arvi Laanemets" wrote: Hi To locate the part of formula, causing the error, try to calculate different parts of formula =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1)) =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY()))) =SUMPRODUCT(Websites!R5:R31) =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ()))) etc. Arvi Laanemets "Pieman" wrote in message ... Avri I have entered this formula: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31). But I still get a popup error message that says: 'The formula you typed contains an error'. Thanks Simon "Arvi Laanemets" wrote: Hi =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31) Arvi Laanemets "Pieman" wrote in message ... Avri, thanks for explaining that. I have entered the following formula for the current year: =SUMPRODUCT("--(MONTH(Websites!B5:B31)=01,--(YEAR(Websites!B5:B31)=YEAR(TODA Y()),Websites!R5:R31)") but I just get #VALUE! in the cell where the monthly commission total should be displayed. Have you any ideas why this is? Thanks Simon "Arvi Laanemets" wrote: Hi SaleDate and Commission are ranges, where sale dates and commission figures are stored. You can use there range references or named ranges. NB. Both ranges SaleDates and Commission MUST be of same dimension. When SalaDate doesn't contain valid format dates, then you have to modify the formula - how, depends on your data then. Arvi Laanemets "Pieman" wrote in message ... Thanks Arvi, does SaleDate have to be replaced by the month required or is that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Hi
I tried to det the same error with same formula, and all partial formulas working properly - I get it only, when referred ranges were different dimensions. Something like =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B131)=YEAR(TODA Y()))) So check, that all referred ranges contain cells from 22 rows, and all of them are single-column ranges. Arvi Laanemets "Pieman" wrote in message ... Avri I have tried all the parts of the formula and the only one that causes the error is: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ()))) All these worked fine: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1)) =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY()))) =SUMPRODUCT(Websites!R5:R31) Any idea where the error is located? Many thanks Simon "Arvi Laanemets" wrote: Hi To locate the part of formula, causing the error, try to calculate different parts of formula =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1)) =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY()))) =SUMPRODUCT(Websites!R5:R31) =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ()))) etc. Arvi Laanemets "Pieman" wrote in message ... Avri I have entered this formula: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31). But I still get a popup error message that says: 'The formula you typed contains an error'. Thanks Simon "Arvi Laanemets" wrote: Hi =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31) Arvi Laanemets "Pieman" wrote in message ... Avri, thanks for explaining that. I have entered the following formula for the current year: =SUMPRODUCT("--(MONTH(Websites!B5:B31)=01,--(YEAR(Websites!B5:B31)=YEAR(TODA Y()),Websites!R5:R31)") but I just get #VALUE! in the cell where the monthly commission total should be displayed. Have you any ideas why this is? Thanks Simon "Arvi Laanemets" wrote: Hi SaleDate and Commission are ranges, where sale dates and commission figures are stored. You can use there range references or named ranges. NB. Both ranges SaleDates and Commission MUST be of same dimension. When SalaDate doesn't contain valid format dates, then you have to modify the formula - how, depends on your data then. Arvi Laanemets "Pieman" wrote in message ... Thanks Arvi, does SaleDate have to be replaced by the month required or is that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating monthly totals
Arvi
Yes, thanks, it works fine now. Still not quite sure where the error was but all is OK now thanks to you. Much appreciated :-) Simon "Arvi Laanemets" wrote: Hi I tried to det the same error with same formula, and all partial formulas working properly - I get it only, when referred ranges were different dimensions. Something like =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B131)=YEAR(TODA Y()))) So check, that all referred ranges contain cells from 22 rows, and all of them are single-column ranges. Arvi Laanemets "Pieman" wrote in message ... Avri I have tried all the parts of the formula and the only one that causes the error is: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ()))) All these worked fine: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1)) =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY()))) =SUMPRODUCT(Websites!R5:R31) Any idea where the error is located? Many thanks Simon "Arvi Laanemets" wrote: Hi To locate the part of formula, causing the error, try to calculate different parts of formula =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1)) =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY()))) =SUMPRODUCT(Websites!R5:R31) =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ()))) etc. Arvi Laanemets "Pieman" wrote in message ... Avri I have entered this formula: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31). But I still get a popup error message that says: 'The formula you typed contains an error'. Thanks Simon "Arvi Laanemets" wrote: Hi =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31) Arvi Laanemets "Pieman" wrote in message ... Avri, thanks for explaining that. I have entered the following formula for the current year: =SUMPRODUCT("--(MONTH(Websites!B5:B31)=01,--(YEAR(Websites!B5:B31)=YEAR(TODA Y()),Websites!R5:R31)") but I just get #VALUE! in the cell where the monthly commission total should be displayed. Have you any ideas why this is? Thanks Simon "Arvi Laanemets" wrote: Hi SaleDate and Commission are ranges, where sale dates and commission figures are stored. You can use there range references or named ranges. NB. Both ranges SaleDates and Commission MUST be of same dimension. When SalaDate doesn't contain valid format dates, then you have to modify the formula - how, depends on your data then. Arvi Laanemets "Pieman" wrote in message ... Thanks Arvi, does SaleDate have to be replaced by the month required or is that the column title? Many thanks Simon "Arvi Laanemets" wrote: Hi To return summary commissions for January of current year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission) The same for last year =SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio n) Arvi Laanemets "Pieman" wrote in message ... Does anyone know the best formula for adding figures in a column that correspond to each month of the year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. Any ideas would be greatly appreciated. Thanks Simon |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Collecting weekly and monthly totals from daily data | Excel Worksheet Functions | |||
| Calculating totals | Excel Discussion (Misc queries) | |||
| How do I sum YTD totals based on monthly totals | Excel Discussion (Misc queries) | |||
| How do I forecast monthly and annual totals from previous year's . | Excel Worksheet Functions | |||
| Displaying YTD totals as you populate monthly information | Excel Worksheet Functions | |||
Linear Mode
