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:

1. Column H = "Incomplete" OR "Provider Missed"
2. Column K = 3/24/14 < Value <= 4/30/14
3. 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!

Answer 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,
"")))