Count number of distinct lists excluding zeros in two-dimensional array

Question

In Excel 2019 I have a formula in a cell which when evaluated with F9 in the formula bar returns the following two-dimensional array (matrix):

{0,0; 0,0; 0,0; 0,0; 0,0; 0,0; 44846,1; 44846,1; 44846,1}

I'd like to nest this result inside another function which would count the number of distinct lists (pair of values, or rows), without counting the zeros. Distinct values means unique values + first occurrences of duplicate values.

So, for the previous 2D array, the formula should return 1, because there's only one unique pair (the 44846,1), without counting the zeros.

For the following 2D array, the formula should return 2, because there's only two unique pair (the 44843,1 and the 44844,1), without counting the zeros:

{44843,1; 44844,1; 0,0; 0,0; 0,0; 0,0; 0,0; 0,0; 0,0}

For the following 2D array, the formula should return 4, because there's only four unique pair (the 44843,1, the 44843,2, the 44845,3, and the 44845,4), without counting the zeros:

{0,0; 0,0; 44843,1; 44843,2; 44845,3; 44845,4; 0,0; 0,0; 0,0}

I'd like such formula to not require Excel 365, VBA, and ideally not be an array formula (i.e. require Ctrl + Shift + Enter).

If you need more background, feel free to ask.

Answer 1

Can't be sure this won't require CSE, though it should work for all Excel versions:

=SUM(
    IF(FREQUENCY(
        IF(MMULT(N(ζ<>0),{1;1}),
            MMULT(
                N(INDEX(ζ,,1)&"|"&INDEX(ζ,,2)=TRANSPOSE(INDEX(ζ,,1)&"|"&INDEX(ζ,,2))),
                ROW(INDIRECT("1:"&ROWS(ζ)))
            )
        ),
        ROW(INDIRECT("1:"&ROWS(ζ)))),1
    )
)

Replace ζ with your array.

Answer 2

You may try this:

If in Row:

=SUM(--(FREQUENCY(IF(A1:C3<>0,MMULT(A1:C3,{1;10^4;10^8})),IF(A1:C3<>0,MMULT(A1:C3,{1;10^4;10^8})))>0))

If in Column:

=SUM(--(FREQUENCY(IF(A1:A10<>0, MMULT((B1:C10<>"")*B1:C10,{1;10^4;10^8})), IF(A1:A10<>0, MMULT((B1:C10<>"")*B1:C10,{1;10^4;10^8})))>0))

• Adjust cell references as needed.