| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting and adding totals
I need help with a formua that will search through the initals in the right
coloumn and add the totals in the left coloumn according to the initials in the right. any suggestions? here is an example of what I am talking about 650.65 VB 197.00 JB 228.00 JB 410.00 KMM 361.53 KMM 3,982.00 VB 2,200.00 VB 498.48 JB 172.00 KMM 511.57 KMM 644.69 VB 184.26 VB 290.00 VB 969.00 KMM 198.00 JB 500.00 PB |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sorting and adding totals
SUMPRODUCT or SUMIF would both accomplish this:
=SUMPRODUCT(--(B1:B100="VB"),A1:A100) =SUMIF(B1:B100,"VB",A1:A100) HTH Elkar "docbehr" wrote: I need help with a formua that will search through the initals in the right coloumn and add the totals in the left coloumn according to the initials in the right. any suggestions? here is an example of what I am talking about 650.65 VB 197.00 JB 228.00 JB 410.00 KMM 361.53 KMM 3,982.00 VB 2,200.00 VB 498.48 JB 172.00 KMM 511.57 KMM 644.69 VB 184.26 VB 290.00 VB 969.00 KMM 198.00 JB 500.00 PB |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sorting and adding totals
|
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sorting and adding totals
docbehr wrote:
I need help with a formua that will search through the initals in the right coloumn and add the totals in the left coloumn according to the initials in the right. any suggestions? here is an example of what I am talking about 650.65 VB 197.00 JB 228.00 JB 410.00 KMM 361.53 KMM 3,982.00 VB 2,200.00 VB 498.48 JB 172.00 KMM 511.57 KMM 644.69 VB 184.26 VB 290.00 VB 969.00 KMM 198.00 JB 500.00 PB One way: Assuming your data in columns A & B, in column C try =SUMIF(B:B,B2,A:A) The possible downside to this easy solution is you get totals on every row, so you have to search through the list to see every person's total. Another way: A pivot table will give a nice summary. First, put column labels in the top row (this is good practice anyway). With your cursor somewhere in the list start up the pivot table wizard, place the "initial" field in the Row area and the "value" field in the data area. Finish. You may need to adjust the "value" summary to be a sum rather then a count. To do this double click the "value" control (probably a gray cell that says "Count of..." and change the option to be Sum. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot table sorting by sub-totals | Excel Discussion (Misc queries) | |||
| sorting by week w/sub totals | Excel Worksheet Functions | |||
| Adding totals to new sheet | Excel Worksheet Functions | |||
| Adding totals after filtering | Excel Discussion (Misc queries) | |||
| Adding category totals | Excel Worksheet Functions | |||
Linear Mode
