| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total weekly wages by month
I have tried using SUMIF but am stuck in 'criteria'. I have two columns with
week date and amount for each employee. I want to total and show the totals for each month. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
Bill,
Prefered method: Use a Pivot table, with the dates as the row fields, and then group by date. No formulas involved. Or, use a formula like =SUMPRODUCT((MONTH(C3:C25)=2)*D3:D25) where column C has the dates, and D the numbers, and you want February results. (This could be modified to make a table by using =SUMPRODUCT((MONTH($C$3:$C$25)=G2)*$D$3:$D$25) Where G2 has a month number, and the numbers continue down column G... Another method. Use a helper column with the formula =MONTH(cell with date) and base the SUMIF on that column. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I have tried using SUMIF but am stuck in 'criteria'. I have two columns with week date and amount for each employee. I want to total and show the totals for each month. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
Have tried 'SUMPRODUCT' formula and what I get in the cell is '#VALUE'. I
must be doing something wrong. The sheet is in employee order, one employee under the last and totalled. I cannot change the order by sorting for a Pivot table. "Bernie Deitrick" wrote: Bill, Prefered method: Use a Pivot table, with the dates as the row fields, and then group by date. No formulas involved. Or, use a formula like =SUMPRODUCT((MONTH(C3:C25)=2)*D3:D25) where column C has the dates, and D the numbers, and you want February results. (This could be modified to make a table by using =SUMPRODUCT((MONTH($C$3:$C$25)=G2)*$D$3:$D$25) Where G2 has a month number, and the numbers continue down column G... Another method. Use a helper column with the formula =MONTH(cell with date) and base the SUMIF on that column. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I have tried using SUMIF but am stuck in 'criteria'. I have two columns with week date and amount for each employee. I want to total and show the totals for each month. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
Bill,
Are the dates actual dates? or strings that look a little like dates? Change the formatting of the cell to number, and the date should change to a number.... Otherwise, you have strings, and need to take other steps to change to actual dates. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... Have tried 'SUMPRODUCT' formula and what I get in the cell is '#VALUE'. I must be doing something wrong. The sheet is in employee order, one employee under the last and totalled. I cannot change the order by sorting for a Pivot table. "Bernie Deitrick" wrote: Bill, Prefered method: Use a Pivot table, with the dates as the row fields, and then group by date. No formulas involved. Or, use a formula like =SUMPRODUCT((MONTH(C3:C25)=2)*D3:D25) where column C has the dates, and D the numbers, and you want February results. (This could be modified to make a table by using =SUMPRODUCT((MONTH($C$3:$C$25)=G2)*$D$3:$D$25) Where G2 has a month number, and the numbers continue down column G... Another method. Use a helper column with the formula =MONTH(cell with date) and base the SUMIF on that column. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I have tried using SUMIF but am stuck in 'criteria'. I have two columns with week date and amount for each employee. I want to total and show the totals for each month. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
I enter the dates as 13/10/06 and it shows in the cell as 13-Oct-06. I want
to retain this format for ease of reading. "Bernie Deitrick" wrote: Bill, Are the dates actual dates? or strings that look a little like dates? Change the formatting of the cell to number, and the date should change to a number.... Otherwise, you have strings, and need to take other steps to change to actual dates. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... Have tried 'SUMPRODUCT' formula and what I get in the cell is '#VALUE'. I must be doing something wrong. The sheet is in employee order, one employee under the last and totalled. I cannot change the order by sorting for a Pivot table. "Bernie Deitrick" wrote: Bill, Prefered method: Use a Pivot table, with the dates as the row fields, and then group by date. No formulas involved. Or, use a formula like =SUMPRODUCT((MONTH(C3:C25)=2)*D3:D25) where column C has the dates, and D the numbers, and you want February results. (This could be modified to make a table by using =SUMPRODUCT((MONTH($C$3:$C$25)=G2)*$D$3:$D$25) Where G2 has a month number, and the numbers continue down column G... Another method. Use a helper column with the formula =MONTH(cell with date) and base the SUMIF on that column. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I have tried using SUMIF but am stuck in 'criteria'. I have two columns with week date and amount for each employee. I want to total and show the totals for each month. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
Bill,
Then the dates are OK. The ranges referenced by the MONTH function part of the SUMPRODUCT can only have dates - no headers, text, etc. or that will throw up an error. In my example formula, =SUMPRODUCT((MONTH($C$3:$C$25)=G2)*$D$3:$D$25) C2 had a header "Dates", and C3 to C25 had actual dates. G2 has the number 2 (for February), and D3:D25 have numbers that I want to sum. D2 had the header "Values"..... HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I enter the dates as 13/10/06 and it shows in the cell as 13-Oct-06. I want to retain this format for ease of reading. "Bernie Deitrick" wrote: Bill, Are the dates actual dates? or strings that look a little like dates? Change the formatting of the cell to number, and the date should change to a number.... Otherwise, you have strings, and need to take other steps to change to actual dates. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... Have tried 'SUMPRODUCT' formula and what I get in the cell is '#VALUE'. I must be doing something wrong. The sheet is in employee order, one employee under the last and totalled. I cannot change the order by sorting for a Pivot table. "Bernie Deitrick" wrote: Bill, Prefered method: Use a Pivot table, with the dates as the row fields, and then group by date. No formulas involved. Or, use a formula like =SUMPRODUCT((MONTH(C3:C25)=2)*D3:D25) where column C has the dates, and D the numbers, and you want February results. (This could be modified to make a table by using =SUMPRODUCT((MONTH($C$3:$C$25)=G2)*$D$3:$D$25) Where G2 has a month number, and the numbers continue down column G... Another method. Use a helper column with the formula =MONTH(cell with date) and base the SUMIF on that column. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I have tried using SUMIF but am stuck in 'criteria'. I have two columns with week date and amount for each employee. I want to total and show the totals for each month. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 12 month Rolling Total | Excel Worksheet Functions | |||
| if i save 25.00 a month for 40 years at 5% a year whats the total | Excel Discussion (Misc queries) | |||
| Excel 2000: sum function automated? | Excel Discussion (Misc queries) | |||
| sort by month with a monthly total | Excel Discussion (Misc queries) | |||
| HELP with this function | Excel Worksheet Functions | |||
Hybrid Mode
