x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 177

# Number of Unique Entries Based on Secondary Criteria

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
sparker1970
• 2
• 2
• 2
1 Solution

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

then copy down.

HTH,
Dan
HelperFile-mod.xlsx
0

Commented:
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 Commented:
Sorry Dan...that gives me the total # of entries but not the total # of unique entries
0

Commented:
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

IT ConsultantCommented:

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

Author Commented:
Worked perfectly!
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.