Count unique value with multi criteria

Question

I'm using excel 2016, and I have sheet with 4 columns: Project, Id, Type, Status

data that I have:

            Project     ID         Type            Status
               A        5       Construction        As Scheduled
               A        5       Construction        As Scheduled
               A        9       Administrative      Done
               A        8       Construction        As Scheduled
               B        2       Construction        Late
               B        2       Construction        Late
               B        7       Construction        Late
               B        7       Construction        Late

I want a formula count Project that has unique Id with same type and status. e.g. Project: "A" , ID: "5,5,9,8" , Type: "Construction" , Status: "As Scheduled" the result should be = 2

You lost me at the "unique Id" part. Can you please give at least three example results from your sample data? — Terry W, Mar 07 '21 at 11:37
I mean if count of in columns project: "A" , Id: "5,5,9,8" , Type: "Construction" , Status: " As Scheduled" result should be = 2 another example: project: "A" , Id: "5,5,9,8" , Type: "Administrative" , Status: " Done" result should be = 1 , example 3 : project: "B" , Id: "2,2,7,7" , Type: "Construction" , Status: " Late" result should be = 2 — Mohsen, Mar 07 '21 at 12:02
I don't understand why you include ID as a criteria since you use **all** of the ID numbers? Shouldn't project criteria be enough (choose only project A, or project B)? If that's the case I would do something like this: `=COUNTIFS(A:A,"A",C:C,"Construction",D:D,"As Scheduled")` — Wizhi, Mar 07 '21 at 12:50

score 1 · Answer 1 · edited Mar 07 '21 at 22:33

1

Hi there are many many ways to do it, and it would be even easy to do if you had Microsoft excel 365. However considering you have excel 2016, you can use formulas as below

in column F

=CONCAT(A2:D2)

in Column G

=COUNTIF($F$2:$F$9,F2)

Sample data with formula

once you got the above all you need to do filter out unique values in Column F

Select Column F

Goto Data -> filter Advanced -> Check Unique records only -> Click OK

The result should be as below

Final result

edited Mar 07 '21 at 22:33

Terry W

3,199
2
8
24

answered Mar 07 '21 at 19:26

Ranga

420
2
11

Thanks, but in Excel 2016 the is no CONCAT formula. and if you can make formula without using filter – Mohsen Mar 08 '21 at 11:06

score 0 · Answer 2 · answered Mar 07 '21 at 22:46

My personal experience is that Pivot Table is powerful in finding unique values.

Assuming your sample data is in the range A1:D9. I will insert a helper column in column E to calculate the unique count for each row. Here is the formula I will enter in cell E2 and drag it down to cell E9:

=1/COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,D2)

Next, I will insert a pivot table base on the above range A1:E9 and set it up as below:

Here is the final outcome:

Let me know if you have any questions :)

is there any formula without using filter or Pivot Table? – Mohsen Mar 08 '21 at 11:06 — Mohsen, Mar 08 '21 at 11:06

Count unique value with multi criteria

2 Answers2