# Top Ten Formula

#### tvman5683

##### Board Regular
I use a formula to identify the top ten items in a list of potential items sorted by a descending PRICE value. There is a quantity coulmn summing up the top ten pieces.
Sometimes an item is sold in quantitiy of 2. If it's item number 10 in the sorted list the formula will not identify but stops at the 9th item. Is there a way to "move next" similar to a VB code stepping through data until it can come up with the toal 10 pieces? here's the formula I have been using. Thanks for any help or ideas.

HTML:
``=SUMIF(\$A\$1:\$A2,A2,\$C\$1:C2)<=10``

 Item PRICE QUANTITY TOP TEN A 2.28 1 TRUE B 2.16 1 TRUE C 1.01 1 TRUE D 0.96 1 TRUE E 0.84 1 TRUE F 0.72 1 TRUE G 0.72 1 TRUE H 0.72 1 TRUE I 0.72 1 TRUE J 0.72 2 FALSE K 0.72 1 FALSE

<TBODY>
</TBODY><COLGROUP><COL><COL span=2><COL></COLGROUP>

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### delos001

##### New Member
Hey,

Try this in the TOP 10 column:

=IF(COUNTIF(\$C\$2:C2,">"&0)<=10,"TRUE","FALSE")

#### tvman5683

##### Board Regular
Thanks for the reply. Here's the result of your formula. It selected the first ten lines only(COUNT) and did not reset at the change in name in column A.
so for the first customer the sum of the trues is 13 instead of 10.

 NAME FCAST SUM OF 10 TOP 10 SMITH 6.48 1 TRUE SMITH 6 1 TRUE SMITH 4.68 1 TRUE SMITH 4.2 2 TRUE SMITH 1.2 2 TRUE SMITH 0.84 2 TRUE SMITH 0.84 1 TRUE SMITH 0.84 1 TRUE SMITH 0.72 1 TRUE SMITH 0.6 1 TRUE SMITH 0.6 1 FALSE SMITH 0.6 1 FALSE JONES 14.28 1 FALSE JONES 11.4 1 FALSE JONES 11.11 1 FALSE JONES 10.92 1 FALSE JONES 8.64 1 FALSE JONES 3.6 1 FALSE JONES 3.24 1 FALSE JONES 3.24 1 FALSE

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=2></COLGROUP>

#### delos001

##### New Member
Sorry. I didn't realize you were sorting by customer too. There are a couple ways to pull out top 10. Unfortunately, I don't know how to do it with only using one column.

The easiest way is to use a PIVOT table which will allow you to automatically get the top 10.

The other way is to use two colums:

 NAME FCAST SUM OF 10 RANK TOP 10 SMITH 6.48 1 1 TRUE SMITH 6 1 2 TRUE

<colgroup><col span="2" style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>
</tbody>

In the Rank column: =IF(A2=A1,D1+IF(C2>0,1,0),IF(C2>0,1,0))
In the TOP 10 column: =IF(D2<=10,"TRUE","FALSE")

If you don't want two columns, you could forgo the TRUE FALSE indicators and go with conditional formatting to highlight top 10 and then you would be able to filter by color (instead of filtering by TRUE FALSE).

Someone with some advanced array knowledge might be able to help you do exactly what you want.

Thanks,

Jason

##### Well-known Member

+1 for PivotTable...

/AJ

#### NeonRedSharpie

##### Well-known Member
Code:
``=IF(SUMIFS(\$C\$2:C2,\$A\$2:A2,\$A2,\$C\$2:C2,">"&0)<=10,"TRUE","FALSE")``

Is this what you're looking for?

#### tvman5683

##### Board Regular

Thanks Jason. I'll look into your suggestions.
Red, your formula does what my exisiting one does. It also stops if the sum exceeds 10 on the last pick.
I'm looking to somehow move beyond that line and continue summing until I hit 10 for each customer in the list.
Like below it stops at 4. Sum would be 11. I need to skip the 4 and pick up the next lines that make 10. In this case it 3 more qty 1s

 CUSTOMER FCAST QTY TOP 10 SMITH 7.56 1 TRUE SMITH 5.4 1 TRUE SMITH 4.08 1 TRUE SMITH 3.84 1 TRUE SMITH 2.76 1 TRUE SMITH 2.28 2 TRUE SMITH 1.32 4 FALSE SMITH 1.2 1 FALSE SMITH 1.2 1 FALSE SMITH 0.96 1 FALSE SMITH 0.84 1 FALSE SMITH 0.72 2 FALSE SMITH 0.72 1 FALSE SMITH 0.6 1 FALSE SMITH 0.6 1 FALSE SMITH 0.6 2 FALSE SMITH 0.6 3 FALSE SMITH 0.6 1 FALSE SMITH 0.6 1 FALSE SMITH 0.6 1 FALSE SMITH 0.6 1 FALSE SMITH 0.59 4 FALSE

<TBODY>
</TBODY><COLGROUP><COL><COL span=3></COLGROUP>

JB

#### NeonRedSharpie

##### Well-known Member
Thanks Jason. I'll look into your suggestions.
Red, your formula does what my exisiting one does. It also stops if the sum exceeds 10 on the last pick.
I'm looking to somehow move beyond that line and continue summing until I hit 10 for each customer in the list.
Like below it stops at 4. Sum would be 11. I need to skip the 4 and pick up the next lines that make 10. In this case it 3 more qty 1s

JB

Then you're probably not going to be able to do that with a formula. The logic required is going to be in UDF or macro/vba territory.

Thanks All

Replies
5
Views
303
Replies
29
Views
2K
Replies
8
Views
423
Replies
7
Views
509
Replies
3
Views
319