Use an array in the range argument of a countif?

[kskyllingmark]

I’m looking for a way to count the number of Permit #s in the lower table—but only once per unique MR #, then put that count in the # of Tickets column in the top table.  The numbers in bold show my desired result, based on the data in the lower table.  I just canʼt pin down a formula to make this work.

To lay out what I’m going for more clearly:

• P-1 has 1 unique MR # associated with it in the lower table.  Therefore, its count in the upper table should = 1 automatically.

• P-2 has 2 unique MR #s associated with it in the lower table.  Therefore, its count in the upper table should = 2 automatically.

• P-3 has 3 unique MR #s associated with it in the lower table.  Therefore, its count in the upper table should = 3 automatically.

• P-4 has 4 unique MR #s associated with it in the lower table.  Therefore, its count in the upper table should = 4 automatically.

Important Notes:

• You canʼt use a dynamic array as the Range argument in a COUNTIF function, else this would already be solved.  (I’ve already tried using various combinations of UNIQUE and COUNTIF, but maybe thereʼs one I’m missing?)

• These are just small pieces of much larger tables in my actual work, and the two columns in the lower table may end up having other columns in between.

• I’ve seen some varying solutions that involve LIST, FILTER, etc., which all rely on index references in the form of {1, 0} etc.  This may be a viable option, but I need it to be bulletproof if the table columns end up getting rearranged.  In other words, I need to be able to reference the range by column name, not by index number.

• VBA and macros are not an option due to company policy.

Thank you for any help you can offer!

[#2]

@kskyllingmark 

=BYROW(A2:A5,LAMBDA(x,COUNTA(UNIQUE(FILTER(B8:B23,A8:A23=x)))))

Does this return the intended result?

[#3 · Accepted solution]

@OliverScheurich I've seen this method used before, and the only potential problem is that if the FILTER function doesn't find a match, COUNTA will incorrectly return a value of 1, because neither FILTER nor UNIQUE can return an empty array.

@kskyllingmark While COUNTIF and COUNTIFS will only accept a range object in the "range" parameter, it is possible to use an array in the "criteria" parameter to generate multiple iterations of COUNTIF for each criterion, then use SUM to return the total (Note: with COUNTIFS, this technique can only be used on one of the criteria parameters). Regarding your sample spreadsheet, you could use the following formula in cell B2 and copy it down:

=SUM(N(COUNTIFS($A$8:$A$23, A2, $B$8:$B$23, UNIQUE($B$8:$B$23))>0))

This method returns TRUE if the result of each COUNTIFS is greater than 0 and FALSE if not. The N function converts the TRUE and FALSE values to 1's and 0's, then SUM returns the total unique count. This can also be made to "spill" the results for each Permit # in range A2:A5 by combining it with BYROW:

=LET(range2, B8:B23,
BYROW(A2:A5, LAMBDA(r,
   SUM(N(COUNTIFS(A8:A23, r, range2, UNIQUE(range2))>0)))))

[#4]

SOLVED!! Thank you so much! I donʼt need to do the result spill because itʼs within a table so it automatically fills down. Might experiment with that as well though, someday.

[#5]

@kskyllingmark 

= LET(
    occurrences,   TAKE(UNIQUE(dataTable),,1),
    matches,       SIGN(occurrences=[@Permit]),
    SUM(matches)
  )

This takes the distinct occurrences of Permit and MR# and matches the list to each [@Permit] in turn, summing 1s to perform the count.