Sorting Ranking and Summing

mianmazher

Active Member
Joined
Jan 24, 2004
Messages
412
Hi All Forum Members
Book1
ABCDEFG
1QualitySizeweightQualitySizeweight
2A22X22X410A18X18X220
3B22X18X31022X18X335
4C18X18X21522X22X430
5A22X22X420B22X18X310
6D18X18X22522X22X440
7C18X18X230C18X18X260
8A22X18X33522X22X430
9B22X22X440D18X18X225
10C18X18X215
11A18X18X220
12C22X22X430
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

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

Look how nicely a pivot table does it:
Book2
ABCDEFG
1QualitySizeweight
2A22X22X410Sumofweight
3B22X18X310QualitySizeTotal
4C18X18X215A18X18X220
5A22X22X42022X18X335
6D18X18X22522X22X430
7C18X18X230B22X18X310
8A22X18X33522X22X440
9B22X22X440C18X18X260
10C18X18X21522X22X430
11A18X18X220D18X18X225
12C22X22X430
Sheet2
 

mianmazher

Active Member
Joined
Jan 24, 2004
Messages
412
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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

Active Member
Joined
Jan 24, 2004
Messages
412

ADVERTISEMENT

Waiting for a solution from Sir Aladin



Regards
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top