Sorting Ranking and Summing

[mianmazher]

Hi All Forum Members

Book1
	A	B	C	D	E	F	G
1	Quality	Size	weight		Quality	Size	weight
2	A	22X22X4	10		A	18X18X2	20
3	B	22X18X3	10			22X18X3	35
4	C	18X18X2	15			22X22X4	30
5	A	22X22X4	20		B	22X18X3	10
6	D	18X18X2	25			22X22X4	40
7	C	18X18X2	30		C	18X18X2	60
8	A	22X18X3	35			22X22X4	30
9	B	22X22X4	40		D	18X18X2	25
10	C	18X18X2	15
11	A	18X18X2	20
12	C	22X22X4	30
Sheet1

I have data in the columns A, B and C


I want the data to be summarized as shown in the range E1:G9


Plz Help



Regards

 

[fairwinds]

Hi,

I know you always want formulas, in this case they will be very complex.

Look how nicely a pivot table does it:

Book2
	A	B	C	D	E	F	G
1	Quality	Size	weight
2	A	22X22X4	10		Sumofweight
3	B	22X18X3	10		Quality	Size	Total
4	C	18X18X2	15		A	18X18X2	20
5	A	22X22X4	20			22X18X3	35
6	D	18X18X2	25			22X22X4	30
7	C	18X18X2	30		B	22X18X3	10
8	A	22X18X3	35			22X22X4	40
9	B	22X22X4	40		C	18X18X2	60
10	C	18X18X2	15			22X22X4	30
11	A	18X18X2	20		D	18X18X2	25
12	C	22X22X4	30
Sheet2

 

[mianmazher]

Thanx Fairwinds for reply

I have already done this by the pivot table appraoch.


I want the formula approach and know that the Complex Formula makers are here to help (Sir Aladin)


Regards

(Where there is a will there is a way)

 

[PaddyD]

"Where there is a will there is a way..."

Having received an effective solution which you reject on account of its approach, it is not unreasonable to expect you to do some research about your prefered method, identify the possible options, try to implement them & come back to the board with problems specific to the method you've adopted.


So, what approach do youthink might work (hint - sumproduct() is probably a good place to start). what problems are you having getting the desired results with the formulas you're trying?

more effort, please...

 

[mianmazher]

Waiting for a solution from Sir Aladin



Regards

 

[TheBuGz]

well Pm Mr Aladin if u want it on Fast Track

 

[mianmazher]

Waiting For Sir Aladins Reply


Regards

 

[PaddyD]

"Waiting For Sir Aladins Reply "

who is no doubt, in a reciprocal fashion, waiting for you to indicate how you've got on trying to solve this yourself.

given the sumproduct() suggestion, what have you tried? how have you got on?