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
FamousMortimerAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor 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
 
barry houdiniConnect With a Mentor 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
 
FamousMortimerAuthor 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
 
Patrick MatthewsCommented:
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.

All Courses

From novice to tech pro — start learning today.