Forum Discussion
Solved
Use an array in the range argument of a countif?
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 ...
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)))))
=BYROW(A2:A5,LAMBDA(x,COUNTA(UNIQUE(FILTER(B8:B23,A8:A23=x)))))
Does this return the intended result?
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)))))
- 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.
