Count of Unique Workorders

[dprobst]

I cannot figure out how to write a formula that will calculate the number of unique W/O numbers
that are related to a single part number. Below is a single part number and a list of Work Orders
that relate to that part. As you can see there are duplicate W/Os in the list. The spreadsheet actually
has 300+ different part numbers and 80 unique work order numbers. I imagine it will be an array formula
using countifs and possible frequency, but I do not know how to write the correct formula. In the list below,
there are 77 unique W/O numbers. I need a formula that will look at part # 8922968 and tell me that
77 W/Os contain that part #. Can anyone help? Thanks. (I am using Excel 2013)





Qty Item# W/O
1 8922968 62594
1 8922968 62599
1 8922968 62600
1 8922968 62601
-1 8922968 62601
1 8922968 62602
1 8922968 62606
1 8922968 62609
1 8922968 62611
1 8922968 62612
1 8922968 62613
1 8922968 62614
1 8922968 62615
1 8922968 62616
1 8922968 62617
1 8922968 62625
1 8922968 62775
1 8922968 62802
1 8922968 62858
1 8922968 62953
1 8922968 63004
1 8922968 63008
1 8922968 63009
1 8922968 63012
1 8922968 63013
1 8922968 63014
1 8922968 63015
1 8922968 63016
1 8922968 63017
1 8922968 63018
1 8922968 63019
1 8922968 63020
1 8922968 63021
1 8922968 63022
1 8922968 63023
1 8922968 63024
1 8922968 63025
1 8922968 63027
1 8922968 63028
1 8922968 63029
1 8922968 63030
1 8922968 63031
1 8922968 63032
1 8922968 63033
1 8922968 63034
1 8922968 63036
1 8922968 63037
1 8922968 63071
1 8922968 63072
1 8922968 63334
-1 8922968 63335
1 8922968 63335
1 8922968 63335
-1 8922968 63336
1 8922968 63336
1 8922968 63336
1 8922968 63337
1 8922968 63338
1 8922968 63339
1 8922968 63340
-1 8922968 63341
1 8922968 63341
1 8922968 63341
1 8922968 63342
1 8922968 63343
1 8922968 63344
1 8922968 63345
1 8922968 63346
1 8922968 63347
1 8922968 63348
1 8922968 63349
1 8922968 63352
1 8922968 63353
1 8922968 63359
1 8922968 63360
1 8922968 63368
1 8922968 63647
1 8922968 63648
1 8922968 63649
1 8922968 63650
1 8922968 63651
1 8922968 63652
1 8922968 63659
1 8922968 63718
1 8922968 63718
77 (sum) 85 (count)

[sandy666]

Don't you think the better way is attach excel file with example BEFORE and AFTER?

[tim201110]

=sum(--(match(c2:c86,c2:c86,)=row(a1:a85)))

[avk]

Suppose your data in "A" "B" "C" column
define name manager as "List"
Firstly go to "formula" tab > click "New" > In name mentioned "List" > In Refers to =Sheet1!$C$2:$C$86
In "E2" formula

Please Login or Register  to view this content.

copy down till blank
Arrary formula hence [shift+ctrl+enter]
Refer attachment.

[jeffreybrown]

Or if later you want to know what W/O's are duplicated, use in D2 copied down...

=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)

Then filter column D by a 1 or use =SUMIF(D2:D86,1) in some cell such as E1