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.

1 Answers

1
Pomul On

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