I want to count the number of routes run by delivery drivers. Some routes get run by more than one driver, thus showing as duplicate values in the (date) column, e.g., col. B on the attached should show 8 routes. This seems pretty straightforward, but I can't hit upon the correct formulas to do it - can anyone point me in the right direction?
Hi trstew,
I call your kind of table a "crosstab" table. You have formatted your table so it is much harder for excel to deal with it. See the attached where I've taken your table and unpivoted it as shown on Sheet 3. This is a much easier table for Excel to deal with. You can then filter by driver. After working with pivot tables a bit, I'd bet you can get all you need after changing the format of your data.
Route problem.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
based on your table try this
=SUMPRODUCT(1/COUNTIF(B3:B17,B3:B17))-1
Great, but how does this work?? Why does it not count cells containing "OFF"?Originally Posted by philaugust2004
Not sure I agree with your results: how do you get 5 routes on Sunday? There are only two DUR/MHH and BC (?)
Try
=SUMPRODUCT(1/COUNTIF(C3:C12,C3:C12))-SUM(IF(COUNTIF(C3:C12,{"PLANT","DISP","OFF","VAC"}),1,0))
This discounts any of the entries in the COUNTIF as the SUMPRODUCT counts All unique entries including those in the COUNTIF part of the formula.
Last edited by JohnTopley; 05-18-2017 at 05:03 PM.
Yowza! Makes me feel really wet behind the ears... I guess I could efficientize the worker formula in row 14 to include an array variable also, correct?
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks