Counting Unique Values within a COUNTIFS

user_9665090 0
Viewed 160

have read a through a few questions that are similair to this but nothing quite the same as what I need.

I have a COUNTIFS formula with, at the moment, 4 criteria. The formula looks up a list of jobs and returns those that are

  1. between a certain date (column N)

  2. within a certain country (column A)

  3. of a certain priority (column J)

     =COUNTIFS('DATA'!$N$2:$N$442,">=1/1/2022",'DATA'!$N$2:$N$442,"<4/1/2022",'DATA'!$A$2:$A$442,"UK",'DATA'!$J$2:$J$442,"High")

The problem I now have is, even after applying the criteria above, my source data still has duplicates. Deleting the duplicate rows is not an option as they are needed for other things.

I need to be able to add another clause that counts the UNIQUE job titles from column H. So for example, applying the above formula at the moments returns 5, when I want it to return 3.

    COUNTRY    START DATE    JOB TITLE    PRIORITY
    UK         01/01/2022    JOB 1        HIGH
    UK         01/01/2022    JOB 1        HIGH
    UK         01/01/2022    JOB 2        HIGH
    UK         01/01/2022    JOB 2        HIGH
    UK         01/01/2022    JOB 3        HIGH
countif excel-formula excel
1 Answers

You can try:

enter image description here

Formula in F3:

=SUM(--(LEN(UNIQUE(FILTER(C2:C6,(B2:B6>=F1)*(B2:B6<=G1)*(A2:A6=H1)*(D2:D6=I1))))>0))

And if COUNTIFS() is a must (maybe due to Excel-version), then try:

=SUM(IF((B2:B6>=F1)*(B2:B6<=G1)*(A2:A6=H1)*(D2:D6=I1),1/COUNTIFS(B2:B6,">="&F1,B2:B6,"<="&G1,A2:A6,H1,D2:D6,I1,C2:C6,C2:C6),0))
user_97581940
Related