Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel 2010 Formula - Count Unique with Multiple Criteria

Posted on 2011-09-23
Medium Priority
341 Views
Last Modified: 2012-05-12
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
0
Question by:FamousMortimer
• 2
4 Comments

LVL 93

Accepted Solution

Patrick Matthews earned 1000 total points
ID: 36588519
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

LVL 50

Assisted Solution

barry houdini earned 1000 total points
ID: 36588699
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

LVL 10

Author Closing Comment

ID: 36589436
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

LVL 93

Expert Comment

ID: 36589654
Well, mine did use a formula, after a fashion :)

Glad to help!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
###### Suggested Courses
Course of the Month14 days, 10 hours left to enroll

#### 577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.