Look up category and sum all that fit into that category

[StVitus]

Hello,
I have a problem that is similar to that posted by some others here but not enough to answer my problem. I have attached an example of the type of problem.

I want to lookup the "colour" that my category fits into, then sum all the numbers of that colour to give a % that my category contributes to that colour.

In my actual spread-sheet, I have 320 "colours" and 1700 categories so a lot of "if" commands or summing the values individually will not be practical. I cannot attach it as it is over 20MB but this example is the exact problem in miniature.

I have tried lookup commands and combination of index and match but the lookup only returns the last number in the list, whereas the index/match returns the 1st number without summing them.

Can anyone help me please?!

Many thanks,

StV.

[Pete_UK]

Try this in H2:

=E2/SUMPRODUCT(($B$2:$B$7=VLOOKUP(D2,$A$2:$B$7,2,0))*($E$2:$E$7))

Hope this helps.

Pete

[oeldere]

With an pivot table.

See the attached file.

[StVitus]

Thanks Pete,
that's great. Seems to work fantastic... just need to apply it to my several thousand lines of data now....

I think I can figure it out from what you put here!

cheers,

St.V

[StVitus]

Many thanks Oeldere, but I think I am going to go with Pete's solution. As I need the layout in a specific way.

Cheers,

R.