| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating totals depending on cell value
I have an expense sheet. The data includes: Item Description, Date, Amount,
Employee I would like to calculate totals for each employee separately. This is how the data looks: Widget 1, 12/23/04, 43.45, Joe Widget 2, 12,24/04, 21.55, Edward Widget 3, 12/01/04, 15.55, Joe How can I do this automatically (each time I make a change). Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating totals depending on cell value
Gary,
If you sort your expense sheet by employee name, you can then use Data | Subtotals to automatically sum numeric data by employee. Regards, Jim Cone San Francisco, USA "Gary Paris" wrote in message ... I have an expense sheet. The data includes: Item Description, Date, Amount, Employee. I would like to calculate totals for each employee separately. This is how the data looks: Widget 1, 12/23/04, 43.45, Joe Widget 2, 12,24/04, 21.55, Edward Widget 3, 12/01/04, 15.55, Joe How can I do this automatically (each time I make a change). Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating totals depending on cell value
Thanks for replying quickly.
Is there a way to process the data without sorting? Maybe loop through the data? Thanks, Gary "Jim Cone" wrote in message ... Gary, If you sort your expense sheet by employee name, you can then use Data | Subtotals to automatically sum numeric data by employee. Regards, Jim Cone San Francisco, USA "Gary Paris" wrote in message ... I have an expense sheet. The data includes: Item Description, Date, Amount, Employee. I would like to calculate totals for each employee separately. This is how the data looks: Widget 1, 12/23/04, 43.45, Joe Widget 2, 12,24/04, 21.55, Edward Widget 3, 12/01/04, 15.55, Joe How can I do this automatically (each time I make a change). Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating totals depending on cell value
Gary,
You can do almost anything, using VBA code in Excel. However a ' SumIf ' formula for each employee would do the job quickly. If your demo data were in range B5:E7 then... =SUMIF(E5:E7,"=Joe",D5:D7) =SUMIF(E5:E7,"=Edward",D5:D7) does the trick. You can expand the ranges to allow for additional entries so... =SUMIF(E5:E77,"=Joe",D5:D77) also works. Regards, Jim Cone "Gary Paris" wrote in message ... Thanks for replying quickly. Is there a way to process the data without sorting? Maybe loop through the data? Thanks, Gary "Jim Cone" wrote in message ... Gary, If you sort your expense sheet by employee name, you can then use Data | Subtotals to automatically sum numeric data by employee. Regards, Jim Cone San Francisco, USA "Gary Paris" wrote in message ... I have an expense sheet. The data includes: Item Description, Date, Amount, Employee. I would like to calculate totals for each employee separately. This is how the data looks: Widget 1, 12/23/04, 43.45, Joe Widget 2, 12,24/04, 21.55, Edward Widget 3, 12/01/04, 15.55, Joe How can I do this automatically (each time I make a change). Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating totals depending on cell value
Sometimes there could be 10 entries, 12 entries, 20 entries, etc... I would
like a way to figure out the totals when the sheet is open or becomes active. It seems using the SUMIF command, I would have to plug in the ranges. I would like to loop through the records and when there is no entry in the "employee" field, I'm done. "Jim Cone" wrote in message ... Gary, You can do almost anything, using VBA code in Excel. However a ' SumIf ' formula for each employee would do the job quickly. If your demo data were in range B5:E7 then... =SUMIF(E5:E7,"=Joe",D5:D7) =SUMIF(E5:E7,"=Edward",D5:D7) does the trick. You can expand the ranges to allow for additional entries so... =SUMIF(E5:E77,"=Joe",D5:D77) also works. Regards, Jim Cone "Gary Paris" wrote in message ... Thanks for replying quickly. Is there a way to process the data without sorting? Maybe loop through the data? Thanks, Gary "Jim Cone" wrote in message ... Gary, If you sort your expense sheet by employee name, you can then use Data | Subtotals to automatically sum numeric data by employee. Regards, Jim Cone San Francisco, USA "Gary Paris" wrote in message ... I have an expense sheet. The data includes: Item Description, Date, Amount, Employee. I would like to calculate totals for each employee separately. This is how the data looks: Widget 1, 12/23/04, 43.45, Joe Widget 2, 12,24/04, 21.55, Edward Widget 3, 12/01/04, 15.55, Joe How can I do this automatically (each time I make a change). Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating totals depending on cell value
Gary,
Use one formula for each employee. Each formula will always show the current total. If you add a new employee, then you will have to add another formula. If you initially make the range large enough (in the formulas) then you won't have to adjust them. Place the formulas above the data, and you won't have to move them as the list grows. Of course, if you have 500 employees, then the question becomes where do you put the totals? That question also applies if VBA is used. Regards, Jim Cone San Francisco, USA "Gary Paris" wrote in message ... Sometimes there could be 10 entries, 12 entries, 20 entries, etc... I would like a way to figure out the totals when the sheet is open or becomes active. It seems using the SUMIF command, I would have to plug in the ranges. I would like to loop through the records and when there is no entry in the "employee" field, I'm done. "Jim Cone" wrote in message ... Gary, You can do almost anything, using VBA code in Excel. However a ' SumIf ' formula for each employee would do the job quickly. If your demo data were in range B5:E7 then... =SUMIF(E5:E7,"=Joe",D5:D7) =SUMIF(E5:E7,"=Edward",D5:D7) does the trick. You can expand the ranges to allow for additional entries so... =SUMIF(E5:E77,"=Joe",D5:D77) also works. Regards, Jim Cone "Gary Paris" wrote in message ... Thanks for replying quickly. Is there a way to process the data without sorting? Maybe loop through the data? Thanks, Gary "Jim Cone" wrote in message ... Gary, If you sort your expense sheet by employee name, you can then use Data | Subtotals to automatically sum numeric data by employee. Regards, Jim Cone San Francisco, USA "Gary Paris" wrote in message ... I have an expense sheet. The data includes: Item Description, Date, Amount, Employee. I would like to calculate totals for each employee separately. This is how the data looks: Widget 1, 12/23/04, 43.45, Joe Widget 2, 12,24/04, 21.55, Edward Widget 3, 12/01/04, 15.55, Joe How can I do this automatically (each time I make a change). Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I add totals from a range of dates depending on the month? | Excel Worksheet Functions | |||
| Calculating totals | Excel Discussion (Misc queries) | |||
| calculating different percentages depending on amount | Excel Discussion (Misc queries) | |||
| Help calculating totals | Excel Discussion (Misc queries) | |||
| Calculating totals if a condition is met | Excel Programming | |||
Linear Mode
