please have a look and help me
Posted by jim on November 08, 2001 5:42 AM
good afternoon,
can someone please help me, I am trying to solve a problem in excel..
I have a sheet of data with for eg:
TYPE COST ACCOUNT
----------------------
1 corp 200pds 6-250
2 CORP 12PDS 6-100
3 HQ 100PDS 6-100
4 CORP 23PDS 6-250
5 HQ 220pds 6-250
how can sort it out so that i get this:
corp 223pds 6-250 (sum of 1 and 3)
corp 12pds 6-100
HQ 100pds 6-100
HQ 220pds 6-250
ie i get a sum of the totals sorted by corp/hq and based on the acct????
Posted by Kevin on November 08, 2001 6:54 AM
What I would do is use the autofilter command to filter your data first by the type column, and then by the account column, to filter out all types and accounts that you do not want included in your total. Then I would use the subtotal() function to sum only the data that remains in the amount column - for example if your amounts for your data were in column A, rows 1-10, your subtotal formula would be: subtotal(9,A1:A10)
Hope this helps,
Kevin TYPE COST ACCOUNT ----------------------
Posted by Juan Pablo on November 08, 2001 6:57 AM
The way your data is set doesn't help you achieve what you're trying to do. I'd suggest putting two extra columns (For what i see your data is in 3 columns, {corp, 223pds, 6-250;corp,12pds,6-100} with formulas as follow.
Column D (For the numbers in 223pds, 12 pds, 100pds, etc.)
=LEFT(B2,LEN(B2)-3)
Column E
=RIGHT(B2,3) & C2
Now you can Subtotal using Column E "For each change in" and Column D as "Sum".
Juan Pablo
TYPE COST ACCOUNT ----------------------
