Count unique values (Excel 2003)

Topic

New Reply

One of our users gave me the attached spreadsheet and said she needs to count the unique values in column B (wonum / aka work order number) for each column L (laborcode – aka foreman) within each column A (loc – aka location)

I tried a pivot table, but it listed each labor code and the number of wonums for each one, not the number of unique.

I tried subtotals, but that didn’t work either.

I tried using the frequency and match formulas, but I don’t know how to use them and I gave myself a headache. Can you please help me?

Reply | Quote

Replies

If it does not have to be a one step process (I not sure it can be a one step unless that step is a macro).

First copy the three columns to another location, rearranging the columns to LOC-laborcode-wonum
Next, while within your new columns, go to Data / Filter > Advanced Filter…
Use the Copy to another location, choose unique records only and then choose you location.
Do another Data / Filter > Advanced Filter… – using the LOC and laborcode columns (youll have to define the range and copy to location again.

After the last filter you can get the unique counts by using the Sumproduct function.
=sumproduct((LocRange=Loc)*(laborcoderRange=laborcode))

See attached for demonstration.

Reply | Quote

That looks great. I sent it to the coworker who was asking for it. Thank you so much. And, thank you for including the instructions. That helps me learn how to do this on my own next time.

Reply | Quote