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

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

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

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

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