Data Consolidation formula

[reuben]

Hi,
I am trying to workout how to consolidate a list on a separate sheet. ie.

Sheet1
A B C
1 Product Category Sales
2 Cat Pets 10
3 Bird Pets 23
4 Bell Misc 44
5 Collar Misc 36
6 Dog Pets 61
7 Ball Toy 33
8 Mirror Toy 64
9 Mouse Pets 24
10 Cat Pets 73
11 Cat Pets 11
12 Dog Pets 34

Sheet2
A
1 Pets
2
3 Cat
4 Bird
5 Dog
6 Mouse

where the formula in Sheet2.A3:A6 looks at the value in Sheet2.A1 and
returns a consolidated list of the values in cell Sheet1.A2:A12.

I know the easiest way to do this is with a Pivot Table but...Any help
appreciated.

Cheers


Reuben

 

[Roger Govier]

Hi Reuben

You're quite right, you should do it with a Pivot Table - why not?

If you do want to do it the hard way<bg>, then in cell B3 of Sheet2
=SUMPRODUCT((Sheet1$B$1:$B$100=$A$1)*(Sheet1!$A$1:$A$100=$A3)*Sheet1!$C$1:$C$100)
Copy down as required