Solved

Number of Unique Entries Based on Secondary Criteria

Posted on 2014-02-03
168 Views
Last Modified: 2014-02-03
I have attached a helper spreadsheet to help explain what I am trying to do...

In column D of Sheet1 I am trying to count the number of unique entries in column D of Sheet2 when Sheet1!A = Sheet2!H

In English...How many different codes did a person use based on the USER_INIT with the result in column D on the row matching each change in user.
HelperFile.xlsx
0
Question by:sparker1970
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
• 2
• 2
6 Comments

LVL 35

Expert Comment

ID: 39830460
Use, in D2
=COUNTIF(Sheet2!\$H:\$H, Sheet1!\$A2)

then copy down.

HTH,
Dan
HelperFile-mod.xlsx
0

LVL 23

Accepted Solution

NBVC earned 500 total points
ID: 39830483
Try:

``````=COUNT(1/FREQUENCY(IF(Sheet2!\$H\$2:\$H\$3283=A2,IF(Sheet2!\$D\$2:\$D\$3283<>"",MATCH(Sheet2!\$D\$2:\$D\$3283,Sheet2!\$D\$2:\$D\$3283,0))),ROW(Sheet2!\$D\$2:\$D\$3283)-ROW(Sheet2!\$D\$2)+1))
``````

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.
0

Author Comment

ID: 39830500
Sorry Dan...that gives me the total # of entries but not the total # of unique entries
0

LVL 23

Expert Comment

ID: 39830524
Have you tried mine above?

An alternative with Array formula, but uses helper column...

In Sheet2!J2 enter:

=COUNTIFS(\$D\$2:\$D2,\$D2,\$H\$2:\$H2,\$H2)

copied all the way down.

then in D2 of Sheet1:

=COUNTIFS(Sheet2!H:H,A2,Sheet2!J:J,1)

copied down.
0

LVL 35

Expert Comment

ID: 39830544
Yup, did not read carefully your question.

NVBC's solution works (the array formula, did not try the helper, but being from NVBC I would say that works too :)
0

Author Closing Comment

ID: 39830917
Worked perfectly!
0

Featured Post

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to transfer values from one userform in excel to another 14 43
Excel VBA Script 9 57
Userform to show a range in excel. 3 33
Combining match and vlookup??? 4 27
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses
Course of the Month5 days, 21 hours left to enroll

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

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