Add column only if row header is an exact match.

[ADL]

Hi, I'm trying to create a spreadsheet which quantifies rectifications needed
in a number of different ctagories. There are 5 different products which all
have the same type of rectification. The 1st column details the type of
product, then there are a number of columns where the number of faults found
(if any) in each different catagory are specified.
What I want to do is have a sum at the bottom of each column for each
product, but for user friendliness (I don't enter the data) I want all the
products to be entered in a random order on the same worksheet. How can I
achieve this?

Product Fault 1 Fault 2 Fault 3 Fault 4
Type 1 1 2
Type 2 1 1
Type 1 1 1
Type 3 1 2
Type 2 1 2

Totals
Type 1 0 1 1 3
Type 2 2 1 0 2
Type 3 0 1 2 0

So that in the simple example above I want a formula to give the totals for
each product type. I hope someone can help!!
Btw, i did work out how to do it by adding extra columns for each fault type
then using the 'IF' function and hiding the columns, but ran out of worksheet
space (there are over 100 different fault types that could occur).

 

[Roger Govier]

Hi

Use a Pivot Table

Assuming XL2003
Place cursor on Product>Data>List>Create>my table has headers
Place cursor on Product>Data>Pivot Table>Finish
On the PT skeleton that appears on a new sheet
Drag Type to the Row area
Drag Fault to the Column Area
Drag Fault to the Data area as Sum of Fault

By creating the List object first, it will grow as you you add more rows of
data to it.
After adding more rows of data, when you return to the PT, place cursor on
any cell within the PT>Right click>Refresh to reflect the changes.
--
Regards
Roger Govier

Hi, I'm trying to create a spreadsheet which quantifies rectifications
needed
in a number of different ctagories. There are 5 different products which
all
have the same type of rectification. The 1st column details the type of
product, then there are a number of columns where the number of faults
found
(if any) in each different catagory are specified.
What I want to do is have a sum at the bottom of each column for each
product, but for user friendliness (I don't enter the data) I want all the
products to be entered in a random order on the same worksheet. How can I
achieve this?

Product Fault 1 Fault 2 Fault 3 Fault 4
Type 1 1 2
Type 2 1 1
Type 1 1 1
Type 3 1 2
Type 2 1 2

Totals
Type 1 0 1 1 3
Type 2 2 1 0 2
Type 3 0 1 2 0

So that in the simple example above I want a formula to give the totals
for
each product type. I hope someone can help!!
Btw, i did work out how to do it by adding extra columns for each fault
type
then using the 'IF' function and hiding the columns, but ran out of
worksheet
space (there are over 100 different fault types that could occur).

__________ Information from ESET Smart Security, version of virus
signature database 4531 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4531 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com