Improve company productivity with a Business Account.Sign Up

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

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
Asked:
sparker1970
  • 2
  • 2
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
Use, in D2
=COUNTIF(Sheet2!$H:$H, Sheet1!$A2)

then copy down.

HTH,
Dan
HelperFile-mod.xlsx
0
 
NBVCCommented:
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))

Open in new window


confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.
0
 
sparker1970Author Commented:
Sorry Dan...that gives me the total # of entries but not the total # of unique entries
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
NBVCCommented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
sparker1970Author 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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now