Sum, index, match two columns and put into ascending order

[SteelMaster]

I've already been blessed from the forum with the formula to lookup this table, and put my entries in ascending order. My issue now is, when my panel lengths are the same, I want to sum the quantity needed, and put them into my work order/cut list as one entry, not 2 of the same length.

Below the tables is what it should look like with the right formula, if possible.

[SteelMaster]

No takers on this Post? Could really use the help.
Thanks.

[SteelMaster]

I only get a #VALUE with this formula. What I need the formula to do is look at the length column, if there are same lengths for some items, then add those qty together, then put then in the list in ascending order of length.

=IF(INDEX($B$4:$B$23,MATCH($F$4:$F$23,ROWS(3:3)),$F$4:$F$23,0)=INDEX($B$4:$B$23,MATCH($F$4:$F$23,ROWS(3:3)),$F$4:$F$23,0),SUMIF(F4:F23,"=F4:F23",B4:B23),"")

[Tony Valko]

I downloaded the file but I don't see a detailed explanation of what you're wanting to do. Be very specific with your explanation.

[SteelMaster]

Hope this makes it little more clear.

Thanks for taking a look.

[Tony Valko]

Try this...

Enter this formula in J3 and copy down to J22:

=IF(K3="","",SUMIF(E$3:E$22,K3,B$3:B$22))

Enter this formula in K3:

=MAX(E3:E22)

Enter this array formula** in K4 and copy down to K22:

=IF(K3="","",IFERROR(1/(1/(MAX(IF(E$3:E$22<K3,E$3:E$22)))),""))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[SteelMaster]

Tony,
I apologize for the late reply, I was working away from the CPU last few days. I will test this as soon as possible, thanks so much for the info.

[SteelMaster]

Hum, doesn't seem to be working quite right. Like it's doubling everything?
I've put the formulas in this example.

Note: There is one more line of Headers in my original, that's why my formulas start at F4, not F3.

Thanks.

[Tony Valko]

Note: There is one more line of Headers in my original, that's why my formulas start at F4, not F3.

In the sample file the data to be analyzed starts on row 3 so the formulas have to use references that start at row 3.

Here's the file using references that start on row 3. The results are correct.

Forum Example (2).xlsx

[kvsrinivasamurthy]

Pl see attached file.

[SteelMaster]

Tony,
I don't understand why yours works, and not mine. I have the same formulas, but have to start at ROW 4 because I have a header row of just info LABELS. Why would that make a difference?

[SteelMaster]

Thanks for the assist, I wil;l see if I can get it to work on my original. Tony V has a couple formulas that work on his sheet he sent back, but don't work on mine for some reason?

[SteelMaster]

AH! I'm an idiot
For got to change the K column to 4. Sorry. My bad.

Thanks for all you help!!

[Tony Valko]

Good deal. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.