EXCEL: Distiinct count of ID with multiple conditions 1

[pmax9999]

I have an Excel dataset comprising around a dozen columns and 900 rows. The REF_ID (col B) contains duplicates due to multiple values in a number of columns (although not the columns that specifically interest me in this exercise).

I need to get a Distinct Count of the REF_IDs (Col B) where Col A (RPT_TYPE) = "IDR_RECD" and Col E (date/time) is during April 2016 (dataset includes data from April to September).

Obviously I can achieve what I want with Pivot Tables but I would really like to get a formula that will do it for me as it will assist with the broader report requirements (and hopefully broaden my Excel skills in the process).

I have googled this extensively, but the results aren't quite what I need and I am struggling to apply them to my specific requirements, or really understand the code provided. I suspect my problem is simply one of "old dog / new trick".

I know of a number of Excel experts here who will be able to provide the solution I am looking for.

Any and all assistance greatly appreciated.

Regards

Pete

 

[SkipVought]

Hi,

Would be nice to have some test data and what result you expect from the given test set.

I doubt that a FORMULA will yield the results that I THINK that you're expecting. That's why we need an example AND your expected results.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's a solution to the question that I THINK you are asking.

Here's my test set
[pre]
RPT_TYPE REF_ID colc cold date/time
IDR_RECD AA 4/1/2016
IDR_RECD BB 4/4/2016
IDR_RECD BB 4/7/2016
IDR_RECD AA 4/10/2016
AA 4/13/2016
CC 4/16/2016
BB 4/19/2016
CC 4/22/2016
AA 4/25/2016
BB 4/28/2016
CC 5/1/2016
IDR_RECD AA 5/4/2016
IDR_RECD BB 5/7/2016
[/pre]

So I add a helper column YYYYMM with this formula...
[tt]
F2: =IF(AND(TEXT(date_time,"yyyymm")="201604",RPT_TYPE="IDR_RECD"),REF_ID,"")
[/tt]
...and here are my results...
[pre]
RPT_TYPE REF_ID colc cold date/time YYYYMM
IDR_RECD AA 4/1/2016 AA
IDR_RECD BB 4/4/2016 BB
IDR_RECD BB 4/7/2016 BB
IDR_RECD AA 4/10/2016 AA
AA 4/13/2016
CC 4/16/2016
BB 4/19/2016
CC 4/22/2016
AA 4/25/2016
BB 4/28/2016
CC 5/1/2016
IDR_RECD AA 5/4/2016
IDR_RECD BB 5/7/2016
[/pre]

Now the formula to calculate the number of unique occurrences...
[tt]
=SUM(IF(FREQUENCY(IF(YYYYMM<>"", MATCH(YYYYMM,YYYYMM,0)),ROW(YYYYMM)-ROW(F2)+1),1))
[/tt]
...entered as an ArrAY FormULA (SHIFT+ctrl+ENTER)
My result is 2.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pmax9999]

Thanks Skip. I really appreciate you taking the time to assist.

Sample data:

[pre]
RPT_TYPE REF_ID ISSUE_TYPE ISSUE DATE_TIME
IDR_CLOSE 453956 Process Biased Process 29/09/2016 13:34
IDR_CLOSE 453956 Process Disagree with decision 29/09/2016 13:34
IDR_CLOSE 454408 Process Unable to understand 27/09/2016 18:48
IDR_CLOSE 454570 Process Delay 22/09/2016 14:41
IDR_RECD 434547 Process Biased Process 04/04/2016 11:45
IDR_RECD 434547 Process Breach of privacy 04/04/2016 11:45
IDR_RECD 434547 Process Incorrect info 04/04/2016 11:45
IDR_RECD 434549 Process Delay 04/04/2016 11:53
IDR_RECD 434568 Determinations Failure 04/04/2016 13:55
IDR_RECD 434579 Determinations Procedural fairness 04/04/2016 14:18
IDR_RECD 434581 Determinations Incorrect assessment 04/04/2016 14:31
IDR_RECD 434906 Process Incorrect information 06/04/2016 09:41
IDR_RECD 434973 Determinations Procedural fairness 06/04/2016 16:10
IDR_RECD 434973 Determinations Failure 06/04/2016 16:10
IDR_RECD 435004 Determinations Failure 06/04/2016 17:09
IDR_RECD 435004 Determinations Incorrect assessment 06/04/2016 17:09
IDR_RECD 435004 Determinations Incorrect information 06/04/2016 17:09
IDR_RECD 435004 Process Biased Process 06/04/2016 17:09
IDR_RECD 444410 Determinations Incorrect assessment 28/06/2016 15:59
IDR_RECD 444410 Process Biased Process 28/06/2016 15:59
IDR_RECD 444412 Other Issues Incorrect information 28/06/2016 16:00
IDR_RECD 444491 Process Failure 29/06/2016 08:12
IDR_RECD 444969 Determinations Unable to understand 01/07/2016 13:18
IDR_RECD 445029 Determinations Bias 04/07/2016 09:47
IDR_RECD 445029 Determinations Procedural fairness 04/07/2016 09:47
IDR_RECD 445269 Staff Related Discourteous 05/07/2016 14:52
IDR_RECD 445270 Staff Related Discourteous 05/07/2016 14:55
IDR_RECD 445427 Process Failure 06/07/2016 10:41
IDR_RECD 445427 Process Unable to understand 06/07/2016 10:41
IDR_RECD 445473 Determinations Bias 06/07/2016 14:10
IDR_RECD 445473 Determinations Procedural fairness 06/07/2016 14:10
IDR_RECD 445473 Determinations Incorrect assessment 06/07/2016 14:10
[/pre]

Result expected: 8

Your solution worked perfectly, and although a 2 step process (using a helper column), was much easier to understand than other solutions I saw.

Thank you very much for your assistance.


Regards
Pete