COUNTIFS from UNIQUE VSTACK

Question

I have a table that I want to count how many occurrences of values in 2 columns there are, based on the value of a third, and for ease of readability, I want to be able to see those unique values with the count next to them.

E.g:

Person1	Person2	Number
Bill	Dave	2
Dave	John	2
John	Bill	1

I want to see how many times the name of each person appears, based on the value of the third column. Now because this is a fluid document, the only columns being updated are those 3, so I want a new column with the unique values of A and B, if the value of C = 2

What I have come up with so far is:

=UNIQUE(VSTACK(A2:A100,B2:B100))

Which works fine, but then counting the occurrences based on the value of C is where I'm stumbling. I have tried

=COUNTIFS(A2:B100,E2,C2:C100,">1")

But it keeps coming up with "A value is the wrong Data Type. I have ensured that the columns are the right types, E.g. C is a number, as well as copying the values from the UNIQUE function into a separate column, so that it's not attempting to stack functions.

I have tried other functions like COUNT(UNIQUE(FILTER()) but that doesn't eliminate the invalid counts, and another solution, I don't know if it works because I can't understand the function:

=SUM(--(FREQUENCY(IF(C2:C18=2,MATCH(A2:A18,B2:B18,0)),ROW(A2:B18)-ROW(A2)+1)>1))

Answer 1

With this input table:

Person1	Person2	Number
Bill	Dave	2
Dave	John	2
John	Bill	1
Bill	John	2
Dave	Bill	2
John	Bill	1
Tom	Bill	2
Bill	John	1
Dave	Bill	2
John	Bill	2

this formula:

=LET(toonecol,VSTACK(HSTACK(A2:A18,C2:C18),B2:C18),
filt,FILTER(toonecol,CHOOSECOLS(toonecol,2)=2),
cnt,BYROW(UNIQUE(TOCOL(A2:B18)),LAMBDA(x,SUM(IF(filt=x,1,0)))),
DROP(HSTACK(UNIQUE(TOCOL(A2:B18)),cnt),-1))

results in this table

-	-
Bill	6
Dave	4
John	3
Tom	1

toonecol contains the two columns of names joined with Number column.
filt contains only with Number=2
cnt counts the unique names occurences
and the last row join unique names with the result.

Answer 2

For fun and if applicable, try to utilize GROUPBY():

=LET(
     a, TOCOL(FILTER(A2:B11,C2:C11=2,"")),
     GROUPBY(a,a,ROWS,,0))

---

Using the data kindly supplied by Black cat

Answer 3

As always there are multiple solutions. Using a table called tblData

=LET(
    uniqueNames,UNIQUE(TOCOL(tblData[[Person1]:[Person2]])),
    cnt,BYROW(uniqueNames,
        LAMBDA(n,
            ROWS(
                FILTER(tblData,(tblData[Number]=2)*((tblData[Person1]=n)+(tblData[Person2]=n)))
                ))),
    HSTACK(uniqueNames,cnt)
    )

Per each unique name of the list tblData is filtered by number and persons name. cnt = number of filtered rows.

Answer 4

Another one:

=LET(a,TOCOL(FILTER(A1:B10,C1:C10=2)),b,UNIQUE(a),c,BYROW(b,LAMBDA(x,SUM(1*(x=a)))),HSTACK(b,c))

Result: