Google Sheets - Count Unique in sheet or using google script

Question

I have a Google Sheet with 2 tabs (Totals and Active).

The Totals tab has a section (A23:F27) that is doing the count unique.

A24:A27 contains the names of individuals
and C23:F23 contains the phase of the project.

Using these 2 section of values, in the Active tab, the individuals names are in column U, the project phase is in column V, and the unique values being counted are in column D.

I have the following array formula that does exactly what I need but having it in 56 cells (maybe more in the future) slows the sheet down to the point it doesn't function.

=ArrayFormula(
 SUM(
  IF(
    FREQUENCY(
      IF(ACTIVE!T:T=$A24,
        IF(
         ACTIVE!V:V=$C$23,
         MATCH(ACTIVE!D:D, ACTIVE!D:D, 0)
        )
      ),
      ROW(ACTIVE!$A$2:$AY)-ROW(ACTIVE!$T$2)+1
    )
  ,1)
 )
)

My question is does anyone have any other solutions? Both formulas and google script are ok.

It's difficult to come up with a better solution when there is no problem statement. A verbal description of what is being calculated, or a sample spreadsheet, or sample input/output data would help. — user3717023

Max Makhrov Max Makhrov · Accepted Answer · 2016-06-06T11:15:43

You have very interesting and hard for understanding formula for counting unique values, I suggest you trying this one:

=COUNTA(UNIQUE(FILTER(ACTIVE!$D:$D,ACTIVE!$T:$T=$A24,ACTIVE!$V:$V=C$23)))

I used filter + unique counstruction. You could easily paste new conditions into this formula: filter(range, condition1, condition2, ..., your condition)

Google Sheets - Count Unique in sheet or using google script

1 Answers