Solved

Number of Unique Entries Based on Secondary Criteria

Posted on 2014-02-03
6
165 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
Comment
Question by:sparker1970
  • 2
  • 2
  • 2
6 Comments
 
LVL 34

Expert Comment

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

then copy down.

HTH,
Dan
HelperFile-mod.xlsx
0
 
LVL 23

Accepted Solution

by:
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))

Open in new window


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

Author Comment

by:sparker1970
ID: 39830500
Sorry Dan...that gives me the total # of entries but not the total # of unique entries
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 23

Expert Comment

by:NBVC
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 34

Expert Comment

by:Dan Craciun
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

by:sparker1970
ID: 39830917
Worked perfectly!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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

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

Join & Ask a Question