countif unique values using multiple criteria

Question

I have read a bunch of other posts, but havent fount what I need. I need to do a countif on 2 criteria, but only count the unique "EventIds" in this case that meet it. currently it counts the total number that meet the criteria

EventID     DR Ref(F)   Number Locations (G)
110000018   1           13
110000018   2           2
110000018   3           8
110000018   4           5
110000252   1           3
110000252   2           3
110000354   1           1
110000366   1           2
110000366   3           1

I have the data above and am trying to display it in a matrix below, currently I am using a countif with F being the "DR Ref" column, "AB" being DR1 a

=COUNTIFS($F$4:$F$12,">="&AB$2,$G$4:$G$12,">="&$Y4)

this is the current output...

            DR
Locat    1(AB)  2   3
> 1        9    5   3
> 2        7    4   2
> 3        5    3   2
> 4        3    2   2
> 5        3    2   2
> 6        2    1   1
> 7        2    1   1
> 8        2    1   1
> 9        1    0   0
> 10       1    0   0
> 11       1    0   0
> 12       1    0   0
> 13       1    0   0

Desired output

this is the wanted output...

            DR
Locat    1(AB)  2   3
> 1        4    3   2
> 2        3    2   1
> 3        2    2   1
> 4        1    1   1
> 5        1    1   1
> 6        1    1   1
> 7        1    1   1
> 8        1    1   1
> 9        1    0   0
> 10       1    0   0
> 11       1    0   0
> 12       1    0   0
> 13       1    0   0

What have you got as criteria in column Y? We need to understand properly what kind of count you are trying to get. — Foxfire And Burns And Burns
Sorry, column Y would be the location , so 1,2,3,4,5 etc, .. its the column in the results matrix — James
I found an answer using a countunique and filter formula in google spreadsheets, but would like to have it in my excel — James

sacse sacse · Accepted Answer · 2018-09-26T16:25:40

Use the following formula:

=SUM(IF(($F$4:$F$12>=AB$2)*($G$4:$G$12>=$Y4),1/COUNTIFS($E$4:$E$12,$E$4:$E$12,$F$4:$F$12,">="&AB$2,$G$4:$G$12,">="&$Y4),0))

and press ctrl+shift+enter

Check the code here.

but I have to say that you phrased the question in a very typical manner.

countif unique values using multiple criteria

1 Answers