SUMPRODUCT that counts unique text values

Question

I have a data set that roughly looks like this:

OpenDate      Name      FillDate      DaysToFill
12/05/13      Samuel        -           Open
01/01/14      Anne      01/16/14        16
01/12/14      Mike      01/25/14        13
01/28/14      Anne      01/31/14         3

I have a dashboard I am creating for metrics. There is a data validation list in which the user picks the month they want information about. This choice triggers a change in two cells -- R2 and R3. R2 corresponds to the month and generates the numeric value of that month (Jan =1, Feb = 2, etc) and R3 generates the last day in that month for the current year (Jan = 01/31/14, Feb = 02/28/14).

I have a formula that counts the number of entries that meet EITHER of the conditions below:

(i) DaysToFill > 0  AND FillDate is in the month chosen (in R2)

OR

(ii) DaysToFill = "Open" AND OpenDate < last day of month chosen (in R3)

I currently have the following sumproduct formula that calculates this:

=SUMPRODUCT(--(ISNUMBER(DATA!I2:I30)),--(MONTH(DATA!I2:I30)=Sheet1!R2),

--(DATA!J2:J30>0))+SUMPRODUCT(--(ISNUMBER(DATA!C2:C30)),

--((DATA!C2:C30)<=Sheet1!R3),--(DATA!J2:J30="Open"))

Where

Column C = OpenDate; Column I = FillDate; Column J = DaysToFill, and Column H (which isn't used here) is Name

This all works fine, but I need a way to add the condition on the above formula -- I need a way to count the unique Names that meet those conditions. So for the above data, if the user chooses January, the output for the formula I have is 4 [3 that meet the conditions in (i) and 1 that meets the conditions in (ii)]. But, if we get the additional layer of unique recruiters, I should get 3 (Because Anne is counted twice). I'm having a hard time seeing how to approach this. Any help is appreciated.

so, Anne should be counted once or shouldn't be counted at all? — Dmitry Pavliv
Anne should be counted once. Its like a true/false -- if a unique name (here, Anne) either had a filled date in that month OR had an "Open" date prior to that month then they are counted once. So Anne is counted once for January. — Mike L

barry houdini barry houdini · Accepted Answer · 2014-03-10T21:16:54

You can use this "array formula"

=SUM(IF(FREQUENCY(IF(ISNUMBER(DATA!I2:I30)*(MONTH(DATA!I2:I30)=Sheet1!R2)*(DATA!J2:J30>0)+ISNUMBER(DATA!C2:C30)*(DATA!C2:C30<=Sheet1!R3)*(DATA!J2:J30="Open"),IF(H2:H30<>"",MATCH(H2:H30,H2:H30,0))),ROW(H2:H30)-ROW(H2)+1),1))

confirmed with CTRL+SHIFT+ENTER

Note that I'm not sure if your original formula has a flaw because DATA!J2:J30>0 will return TRUE for any cells that contain "Open". Does that extra condition achieve anything, if J2:J30 always contains a date or "Open" that part will return TRUE for everything.....

SUMPRODUCT that counts unique text values

2 Answers