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