# Summarizing data

1. ## Summarizing data

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  Register To Reply

2. ## RE: Summarizing data

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

"Pedro AM" wrote:

> 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
>  Register To Reply