excelexcel-formulacountif

COUNTIFS from UNIQUE VSTACK


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))

Solution

  • 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.