Calculating totals depending on cell value

[Gary Paris]

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

 

[Jim Cone]

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]

Thanks for replying quickly.

Is there a way to process the data without sorting? Maybe loop through the
data?

Thanks,

Gary

 

[Jim Cone]

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",D57)
=SUMIF(E5:E7,"=Edward",D57)

does the trick. You can expand the ranges to allow for
additional entries so...

=SUMIF(E5:E77,"=Joe",D577)

also works.

Regards,
Jim Cone

 

[Gary Paris]

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]

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