sorting/puting items in a table based on the values from another table

Hi.

I want to sort in a table the high runners and the low runners. All items that are being produced more than 500 pcs per week should go in the high runners table and all that don't, in the low runners table? How can i make that in Excel?

See attachment for experimenting and exemplification of my problem.
Thank you in advance.

Do cols b, c d, e and f represent weeks?

is the data in col a a list of runners?

for example ---- is 305683720162-AC

a high runner in week one and a low runner in week two?

You need to explain your data a bit better

I'm sure there is a better way of doing this using sumproduct with if but i dont know how to.... so im calling in the cavalry! however, this solution should work.

In G1 enter

=SUM(IF($A$1:$A$24=A1,$B$1:$F$24)) hit CSE

in col H

=IF(G1>500,A1,"") (list of high runners)

In Col I

=IF(G1<500,A1,"") (list of low runners)

Hope this helps

You can perhaps add a helper column... so in G1:

=SUM(B1:F1)

copied down

Then, in K4 add a count:

=COUNTIF(G1:G24,">500")

in K14:

=COUNTIF($G$1:$G$24,"<=500")-COUNTIF($G$1:$G$24,0)

(this assumes you don't want the 0 runners.. if you do remove the second COUNTIF)

then in L5:

=IF(ROWS($A$1:$A1)>$K$4,"",INDEX($A$1:$A$24,SMALL(IF($G$1:$G$24>500,ROW($A$1:$A$24)-ROW($A$1)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER and copied down.

in L12:

=IF(ROWS($A$1:$A1)>$K$11,"",INDEX($A$1:$A$24,SMALL(IF($G$1:$G$24<=500,ROW($A$1:$A$24)-ROW($A$1)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER and copied down.

N - thanks so much for looking at this.

Dont know why I thought that a sumproduct would work on this, guess i dont understand SP v well!!

I will now study your option!

All the best..........

BTW are you on the list?

https://mvp.support.microsoft.com/co...mpetency=Excel

I used the link you sent! thanks

Blake, I sent you a PM link to my profile.

As you marked the thread as solved there is a possibility that N (who wrote the formula) and other people will skip this post.

May I suggest that you start a new thread and attach the spreadsheet.

It is column G that is looked at in my formulas (i.e. the sums of columns B:F) When I made it so the sums of G1:G5 where each 2000 and the sum of G6 was 1, it works in both tables as expected. See attached.

Blake 7, marking the thread Solved doesn't deter us from returning to the thread. Most of us maintain our subscribed status to the thread and will know when a thread has been continued, whether previously marked solved or not. If after a certain amount of time (a day or so) no response received, then you can either bump and try again or start a new thread with a link to the solved thread for continuity.