How to count unique values only if two other columns match to specific values

Question

I am trying to count the distinct ID values whenever location and date equal specific values.

There are a handful of articles/questions out there that outline a few different ways to do this (e.g. How to count unique values in Excel with two conditions), but for some reason none seem to be working for me (which is likely because I'm doing something wrong).

My table in excel is set up like so

+------------------+--------------+------------+
| Col A - Location | Col B - Date | Col C - ID |
+------------------+--------------+------------+
|         A        |   Mar 2018   |      1     |         
|         B        |   Mar 2018   |      2     |
|         C        |   Mar 2018   |      3     |
|         A        |   Mar 2018   |      4     |
|         B        |   Mar 2018   |            |
|         C        |   Mar 2018   |      5     |
|         A        |   Mar 2018   |      6     |
|         B        |   Apr 2018   |      1     |
|         C        |   Apr 2018   |      2     |
|         C        |   Apr 2018   |      4     |
|         C        |   Mar 2018   |      5     |
|         A        |   Mar 2018   |      1     |
|         B        |   Mar 2018   |      5     |
|         B        |   Mar 2018   |      8     |
|         B        |   Mar 2018   |            |
|         C        |   Mar 2018   |      1     |
|         B        |   Mar 2018   |      3     |
+------------------+--------------+------------+

I tried 4 different calculations I found online and altered to fit my data:

Calc 1 (run by just hitting ENTER):
=COUNTIFS(A:A, "A",C:C,C:C,B:B, "Mar 2018")

Result = 2


Calc 2 (running by hitting CTRL + SHFT + ENTER):
=SUMPRODUCT( ( (C:C <> "") * (A:A = "A") * (B:B = "Mar 2018") ) / COUNTIFS(C:C,C:C  & "",A:A, "A",B:B, "Mar 2018"))

Result = 0


Calc 3 (running by hitting CTRL + SHFT + ENTER):
=SUM(IF(FREQUENCY(IF(C:C<>"", MATCH(C:C,C:C,0)),ROW(C:C)-ROW(C2)+1),1))

Result = 0


Calc 4 (running by hitting CTRL + SHFT + ENTER):
=SUM(IF(FREQUENCY(IF((A:A="A")*(B:B="Mar 2018")*(C:C<>""),MATCH(C:C,C:C,0)),ROW(C:C)-MIN(ROW(C:C))+1),1))

Result = 0 
(This one keeps giving an error message about running out of resources while trying to calculate)

The actual results I'm trying to find is that for 'Col A - Location' where the value is 'A' and for 'Col B - Date' where the value is 'Mar 2018' the unique # of 'Col C - ID' should be 3.

Pomul · Accepted Answer · 2019-05-13T14:23:45.113

Easiest way to count with multiple criteria is using SUMPRODUCT:

=SUMPRODUCT(--(A1:A20="A"),--(B1:B20=DATE(2018,3,1)),--(C1:C20=3))

the double negative (--) converts the True/False answers into 1 or 0 for sumproduct to do its job.

I suggest that you limit your ranges (i.e. don't use whole columns like A:A) because it will slow the formula (a lot). Also, it might be easier to use a locked reference cell for your conditions. If you input your conditions in the first row, you could use:

=SUMPRODUCT(--(A2:A20=$A$1),--(B2:B20=$B$1),--(C1:C20=$C$1))

Edit

Following beatrixb's clarification, you would need a helper column to concatenate the three columns. Assurming your data start at A2, you would put this in D2 (if you don't already have a unique identifier for each row):

=A2&B2&C2

And use this formula in another cell to return the number of unique values based on two conditions:

=SUMPRODUCT(--(A2:A20=$A$1),--(B2:B20=$B$1),(1/COUNTIF(D2:D20,D2:D20)))

thank you, this gets me closer, but not 100% there. I don't want C1:C20 to be equal to any specific value, what I want is to count the unique ID #s for Col C, so in my example given the condition of Col A = 'A' and Col B = 'Mar 2018', there are 4 rows that hit those conditions containing IDs 1, 4, 6, 1, and then since 1 is duplicated, the unique count of IDs would be 3. Is there a way to do a countif as the last sumproduct variable? — beatrixb, May 10 '19 at 20:10

How to count unique values only if two other columns match to specific values

1 Answers1