| 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
I have details of 15 employees with totals of wages for each. I have tried
SUMPRODUCT((MONTH(B13:B17,B197:B228)=2)*(C13:C17,C 197:C228)) to no avail. Also tried SUMIF(B13:B17,B197:B228),MONTH(B13:B17,B197:B228), (C13:C17,C197:c228)) without success. Tried + and * and ; instead of , as seperator - no good. What can I do please? "Bernie Deitrick" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
Bill,
=SUMPRODUCT((MONTH(B13:B17)=2)*(C13:C17)) + SUMPRODUCT((MONTH(B197:B228)=2)*(C197:C228)) You have to have contiguous ranges.... HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I have details of 15 employees with totals of wages for each. I have tried SUMPRODUCT((MONTH(B13:B17,B197:B228)=2)*(C13:C17,C 197:C228)) to no avail. Also tried SUMIF(B13:B17,B197:B228),MONTH(B13:B17,B197:B228), (C13:C17,C197:c228)) without success. Tried + and * and ; instead of , as seperator - no good. What can I do please? "Bernie Deitrick" wrote: 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
Many thanks Bernie. I don't believe this is a good solution because I have up
to 20 ranges to cover which would give 820 characters in the instruction line (I showed 2 ranges as an example). Maybe a Pivot Table in the same sheet lower down would work but each week many ranges will be increased by one row (the wages for that week) and the Pivot Table would have to be adjusted automatically or with a macro. This seems to be getting cumbersome. Many thanks anyway for your assistance and forbearance. Bill "Bernie Deitrick" wrote: Bill, =SUMPRODUCT((MONTH(B13:B17)=2)*(C13:C17)) + SUMPRODUCT((MONTH(B197:B228)=2)*(C197:C228)) You have to have contiguous ranges.... HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I have details of 15 employees with totals of wages for each. I have tried SUMPRODUCT((MONTH(B13:B17,B197:B228)=2)*(C13:C17,C 197:C228)) to no avail. Also tried SUMIF(B13:B17,B197:B228),MONTH(B13:B17,B197:B228), (C13:C17,C197:c228)) without success. Tried + and * and ; instead of , as seperator - no good. What can I do please? "Bernie Deitrick" wrote: 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Total weekly wages by month
Bill,
It's easy to automatically update the range for a pivot table. Let's say that your current data is in rows 1 to 100. Set your Pivot table to reference rows 1 to 101, and then wehen you need to add data, insert enough rows between rows 100 and 101 (by selecting row 101 and doing the insert) to hold the new data. Row 101 will be moved down (and should remain blank) and Excel will automatically expand the PT reference range to suit. It's a good idea to format the last row with a special color, and then you'll remember that you need to insert rows BEFORE that row to expand the PT range. HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... Many thanks Bernie. I don't believe this is a good solution because I have up to 20 ranges to cover which would give 820 characters in the instruction line (I showed 2 ranges as an example). Maybe a Pivot Table in the same sheet lower down would work but each week many ranges will be increased by one row (the wages for that week) and the Pivot Table would have to be adjusted automatically or with a macro. This seems to be getting cumbersome. Many thanks anyway for your assistance and forbearance. Bill "Bernie Deitrick" wrote: Bill, =SUMPRODUCT((MONTH(B13:B17)=2)*(C13:C17)) + SUMPRODUCT((MONTH(B197:B228)=2)*(C197:C228)) You have to have contiguous ranges.... HTH, Bernie MS Excel MVP "BillMcSweeney" wrote in message ... I have details of 15 employees with totals of wages for each. I have tried SUMPRODUCT((MONTH(B13:B17,B197:B228)=2)*(C13:C17,C 197:C228)) to no avail. Also tried SUMIF(B13:B17,B197:B228),MONTH(B13:B17,B197:B228), (C13:C17,C197:c228)) without success. Tried + and * and ; instead of , as seperator - no good. What can I do please? "Bernie Deitrick" wrote: 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 | |||
Linear Mode
