Count unique values with criteria

Question

For this Google spreadsheet I would like to do the following:

Count the Unique "Provider User IDs" (column C) that meet the following criteria:

Column H = "Incomplete" OR "Provider Missed"
Column K = 3/24/14 < Value <= 4/30/14
Column X = "School 1"

The result should return "2"

I believe I know how to do this for standard excel but I'm having trouble getting it to return what I expect for Google Sheets. Any help would be greatly appreciated!

It's embedded in the second word of the post – Robin Gertenbach Oct 24 '15 at 22:36 — Robin Gertenbach, Oct 24 '15 at 22:36

score 1 · Accepted Answer · answered Oct 24 '15 at 22:35

1

This is a bit annoying with conditionals because they just swallow all elements in the arrays you provide them.
You need to work around that, for example by using the fact that TRUE() * 1 == 1 and FALSE * 1 == 0:

=COUNTUNIQUE(ARRAYFORMULA(IF(
  (H2:H25 = "Incomplete")             * 1 +
  (H2:H25 = "Provider Missed")        * 1 +
  (DATEVALUE("2014-03-24") <  K2:K25) * 1 + 
  (DATEVALUE("2014-04-30") >= K2:K25) * 1 +
  (X2:X25 = "School 1")               * 1 = 4,
C2:C25,
"")))

answered Oct 24 '15 at 22:35

Robin Gertenbach

10,316
3
25
37

Thanks Robin, that did the trick! I thought I was never going to figure that one out! – Jeremy Loya Oct 26 '15 at 22:07
If this helped to solve your problem can you mark the answer as accepted so people in the future can identify that this is a way to solve it? Thanks – Robin Gertenbach Oct 26 '15 at 22:30

score 0 · Answer 2 · answered Oct 25 '15 at 07:14

0

Following formula will give you all the "Provider User ID" which fulfill the criteria:

=filter(C:C,(H:H="Incomplete")+(H:H="Provider Missed"),(DATEVALUE("2014-03-24")<K:K)+(DATEVALUE("2014-04-30")>=K:K),(X:X="School 1"))

And then to count the unique "Provider User ID", try the following formula:

=countunique(iferror(filter(C:C,(H:H="Incomplete")+(H:H="Provider Missed"),(DATEVALUE("2014-03-24")<K:K)+(DATEVALUE("2014-04-30")>=K:K),(X:X="School 1"))))

answered Oct 25 '15 at 07:14

Kishan

1,630
13
19

I think you are on the right track here, I just can't get the "date" part of the filter to work. It is giving me the unique count for all dates. – Jeremy Loya Oct 26 '15 at 21:49

Count unique values with criteria

2 Answers2