Excel 2010 Formula - Count Unique with Multiple Criteria

Hi Experts,

I am trying to get a count of unique job #'s in col H for each day in col C for each machine # in col I.  I would like to accomplish this using a formula.

If you look at sheet 2 you will see what i mean.  The data in sheet 2 should be the count of unique job #'s on the day specified in col A where the machine number = the corresponding value in row 2.

Book2.xlsx

Let me know if you have any questions.

Thanks
-Jeremy
LVL 10
Asked:
Who is Participating?

Commented:
Jeremy,

Please do the following:

1) In J1, add the heading Distinct

2) On the ribbon, navigate to Insert / Tables / Table to convert your source data into a table

3) In J2 enter this formula:

=1/COUNTIFS([date],[@date],[Machine '#],[@[Machine '#]],[Job '#],[@[Job '#]])

That should update the rest of the column.

4) Create a PivotTable, with Date and Machine# as row labels, and (sum of) Distinct as the data field to get the number of distinct jobs per date/machine.

Also, for some tips on keeping the PT up to date:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html

Patrick
0

Commented:
Hello Jeremy,

You can do that with a formula alone if you want, i.e. with this formula in B3

=SUM(IF(FREQUENCY(IF(Sheet1!\$C\$2:\$C\$2000=\$A3,IF(Sheet1!\$I\$2:\$I\$2000=B\$2,IF(Sheet1!\$H\$2:\$H\$2000<>"",MATCH(Sheet1!\$H\$2:\$H\$2000,Sheet1!\$H\$2:\$H\$2000,0)))),ROW(Sheet1!\$H\$2:\$H\$2000)-ROW(Sheet1!\$H\$2)+1),1))

confirmed with CTRL+SHIFT+ENTER and copied across and down. I formatted so that zeroes don't display [ custom format 0;; ]

.....but it's quite resource intensive - it took 35 seconds to update for your whole table of dates - Patrick's approach might be practically better, especially for larger amounts of data.

See attached, updated sheet

regards, barry
27324617.xlsx
0

Author Commented:
Thanks a lot guys, the sheet could potentially have 50k+ rows so mp's solution would be the most practical however, i asked for a solution using a formula, so thanks a lot to the both of you.
0

Commented:
Well, mine did use a formula, after a fashion :)

Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Already a member? Login.

All Courses

From novice to tech pro — start learning today.