Summarizing data

[Guest]

Hi all

I need to summarze data in this way:
I have a database with items and in Column A and their Category in Column B
plus dta in C
E.G
Item category Data
a X 5
b Z 1
c X 3
d X 6
I want it to look at the item and pick up the data in C and reclassify
according to another table where
Item category Data
a X 5
b X 1
c X 3
d Z 6
But summarised so I have totals for X and Z which would be 9 and 6 as
opposed to 14 and 1.
Needles to say that I have many more categories and much more data for this.

Does anyone know a way to do this bearing in mind that the data table is in
a different file to the table that contains the output and the equivalencies
(e.g d was X and now Z).

Any help would be appreciated. Thank you

 

[Guest]

Here is "model solution" you could adapt. I couldn't find a way to combine
the conversion of categories and the summation of a category into a single
formula. This doesn't mean it cannot be done!

Orginal table in A1 to C5

Item Category Data
a X 5
b Z 1
c X 3
d X 6

Conversion in G1 to I5
Item Old Cat New Cat
a X X
b Z X
c X X
d X Z

New table in G9 to G13

Item Category Data
a X 5
b X 1
c X 3
d Z 6

in H10: =INDEX($I$2:$I$5,MATCH(1,(A2=$G$2:$G$5)*(B2=$H$2:$H$5),0))

Enter with Ctrl+shift + enter (an array formula) and copy down
in I10: = C2 and copy down


Total in g16 to H18

Category Total
X 9
Z 6


in H17: =SUMIF($H$10:$H$13,G17,$I$10:$I$13) and copy down

HTH