CODEWITHSUNDEEP
excelvba
rvphx
rvphx

Reputation: 2402

Dynamically determining range to apply formula/function in EXCEL

I need to determine the range to apply the Frequency function. Here's what the problem is. On the given sheet, I have subtotals for my data and there is a column which has "Stop" Values. The data would look something like:

Route1
Order#                          Stop#   Qty 
001016                           1       5
008912                           1       5
062232                           2       6
062232                           3       2
069930                           4       1
1000                             4       3
1001                             4       4
1001                             5       8
1003                             8       1

Route 1 Subtotal                 6       35 

Route2
Order#                          Stop#   Qty 
10065                             1      5
10076                             1      5  
10077                             2      6
10079                             3      2
10087                             4      1
10098                             4      3
10109                             4      4
10171                             5      8
10175                             8      1
Route 2 Subtotal                  6     35

How do I write VBA code for calculating the distinct stop values. I need the distinct count of the stop#. Hence in the example above you can see that the total stops are 6 because 1 stop can have multiple orders and 1 route can have multiple orders/stop. Hope I am making sense here. Let me know how I would write my VBA code for this. Thanks for your help.

Upvotes: 0

Views: 970

Answers (1)

lori_m
lori_m

Reputation: 5577

For the Stop Subtotal unique count, try this formula (adjust ranges as required):

=COUNT(1/FREQUENCY(B2:B10,B2:B10))

Upvotes: 1

Related Questions