| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidating Data
I have a 12,000 row spreadsheet that contain sales tax received by various
businesses. One particular business may have 2 records or 20 records. Also, there are two types of sales tax which are all in the same field COMPANY NAME TAX TYPE AMOUNT X 1 3.00 X 2 4.00 X 2 1.00 Y 1 5.00 Y 2 4.00 How can I summarize this data where I can see what the totals are for each business for each tax type (preferably where the sum each tax type is in a separate field) -- Gator |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Consolidating Data
Try the SUMPRODUCT function:
=SUMPRODUCT(--(A1:A12000="X"),--(B1:B12000=1),C1:C12000) HTH, Elkar "Gator" wrote: I have a 12,000 row spreadsheet that contain sales tax received by various businesses. One particular business may have 2 records or 20 records. Also, there are two types of sales tax which are all in the same field COMPANY NAME TAX TYPE AMOUNT X 1 3.00 X 2 4.00 X 2 1.00 Y 1 5.00 Y 2 4.00 How can I summarize this data where I can see what the totals are for each business for each tax type (preferably where the sum each tax type is in a separate field) -- Gator |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Consolidating Data
like this......
COMPANY NAME TAX TYPE 1 TAX TYPE 2 X 3 5 Y 5 4 .....see below for part one of question -- Gator "Gator" wrote: I have a 12,000 row spreadsheet that contain sales tax received by various businesses. One particular business may have 2 records or 20 records. Also, there are two types of sales tax which are all in the same field COMPANY NAME TAX TYPE AMOUNT X 1 3.00 X 2 4.00 X 2 1.00 Y 1 5.00 Y 2 4.00 How can I summarize this data where I can see what the totals are for each business for each tax type (preferably where the sum each tax type is in a separate field) ....Like this -- Gator |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Consolidating Data
Off the FileMenu, try DataSubtotal and filter it accordingly.
"Gator" wrote: I have a 12,000 row spreadsheet that contain sales tax received by various businesses. One particular business may have 2 records or 20 records. Also, there are two types of sales tax which are all in the same field COMPANY NAME TAX TYPE AMOUNT X 1 3.00 X 2 4.00 X 2 1.00 Y 1 5.00 Y 2 4.00 How can I summarize this data where I can see what the totals are for each business for each tax type (preferably where the sum each tax type is in a separate field) -- Gator |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Consolidating Data
The Pivot Table works best
-- Gator "Gator" wrote: I have a 12,000 row spreadsheet that contain sales tax received by various businesses. One particular business may have 2 records or 20 records. Also, there are two types of sales tax which are all in the same field COMPANY NAME TAX TYPE AMOUNT X 1 3.00 X 2 4.00 X 2 1.00 Y 1 5.00 Y 2 4.00 How can I summarize this data where I can see what the totals are for each business for each tax type (preferably where the sum each tax type is in a separate field) -- Gator |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merging/Consolidating data? | Excel Discussion (Misc queries) | |||
| Consolidating a Row of data from many worksheets | Excel Worksheet Functions | |||
| need help in consolidating multiple data | Excel Discussion (Misc queries) | |||
| Consolidating data?? | Excel Worksheet Functions | |||
| Pivottable - consolidating data - | Excel Worksheet Functions | |||
Linear Mode
