INDEX, MATCH, LARGE and/or SUMIF/SUMPRODUCT

B

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
I am looking for a formula that will assist in the last two columns


-the first two columns is the raw data
-the third and fourth columns is a distinct for the class name and a sumproduct
-the last two is an Index/match/large

I am looking for an all in one formula and haven't gotten any. I have also searched all over the place and cannot seem to find such a combination.





[TABLE="width: 777"]
<tbody>[TR]
[TD]Class[/TD]
[TD]Values[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 1[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Class 1[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]Class 10[/TD]
[TD]987[/TD]
[/TR]
[TR]
[TD]Class 2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Class 2[/TD]
[TD]836[/TD]
[TD][/TD]
[TD]Class 2[/TD]
[TD]836[/TD]
[/TR]
[TR]
[TD]Class 3[/TD]
[TD]65[/TD]
[TD][/TD]
[TD]Class 3[/TD]
[TD]83[/TD]
[TD][/TD]
[TD]Class 4[/TD]
[TD]285[/TD]
[/TR]
[TR]
[TD]Class 4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Class 4[/TD]
[TD]285[/TD]
[TD][/TD]
[TD]Class 3[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]Class 5[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]Class 5[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]Class 13[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]Class 3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Class 6[/TD]
[TD]36[/TD]
[TD][/TD]
[TD]Class 12[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]Class 6[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]Class 7[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]Class 6[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]Class 7[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]Class 8[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Class 7[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Class 3[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]Class 9[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]Class 15[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Class 4[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Class 10[/TD]
[TD]987[/TD]
[TD][/TD]
[TD]Class 5[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Class 7[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Class 11[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Class 9[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Class 4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Class 12[/TD]
[TD]41[/TD]
[TD][/TD]
[TD]Class 9[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Class 2[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]Class 13[/TD]
[TD]44[/TD]
[TD][/TD]
[TD]Class 14[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Class 8[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Class 14[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]Class 1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Class 4[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]Class 15[/TD]
[TD]18[/TD]
[TD][/TD]
[TD]Class 18[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Class 4[/TD]
[TD]267[/TD]
[TD][/TD]
[TD]Class 16[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]Class 17[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Class 9[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]Class 17[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Class 8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]800[/TD]
[TD][/TD]
[TD]Class 18[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Class 8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Class 11[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Class 19[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Class 11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 2[/TD]
[TD]722[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 12[/TD]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 13[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 14[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 15[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 16[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 17[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 10[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 2[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 18[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 6[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 6[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 13[/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 6[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 19[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 15[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
So basically you are trying to bring six columns down to four, is that correct?

And you want to accomplish this by a formula that will sum all values for each class, then sort from largest to smallest, correct?
 
Upvote 0
I am looking for an all in one formula and haven't gotten any.
Click to expand...

Why? I have found that more often than not, when faced with a complex problem like this, it is better to break it down the way you have. Even if there was a formula to do all that in 1 go, it would probably be so complex as to make troubleshooting or adjusting a big problem
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Copy non-empty cells from the same column from multiple sheets to a sheet "Breeders"
Replies
6
Views
122
mumps
mumps
Z
  • Solved
=SUMIFS help matching 2 names
Replies
4
Views
89
Sergius
S
D
  • Solved
Trying to compare two columns with text and number
Replies
5
Views
222
Davebro
D
S
  • Question Question
VBA code HTML text transformation
Replies
4
Views
251
Sia
S
H
  • Solved
Copy or Transpose multiple columns into a single column.
Replies
2
Views
127
harzer
H

Share this page

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
Back
Top