In this specific case I want to count how many times a set of variables combines in the same line, over 5 different columns, using excel, without macros.
So far I'm using multiple =COUNTIFS(), but this feels cumbersome and bruteforced.
Is there a more efficient/elegant way to solve this problem?
Example data in A1
| Modality wk 1 | Mon | Tue | Wed | Thu | Fri |
|---|---|---|---|---|---|
| Mod1 | John | Karen | John | Joe | Lizzie |
| Mod2 | Karen | Lizzie | Karen | John | John |
| Modality wk 2 | Mon | Tue | Wed | Thu | Fri |
|---|---|---|---|---|---|
| Mod1 | Joe | Karen | John | Karen | Lizzie |
| Mod2 | Karen | Joe | Karen | Karen | John |
To create a table like this, located in A10
| Names | Mod1 | Mod2 |
|---|---|---|
| John | 3 | 3 |
| Karen | 3 | 5 |
| Lizzie | 2 | 1 |
| Joe | 2 | 1 |
I'd have to use a formula like this in B11 and drag to fill the columns and rows (the $ were put to help with the drag and drop):
=COUNTIFS($A$1:$A$6,B$10,$C$1:$C$6,$A11)+COUNTIFS($A$1:$A$6,B$10,$D$1:$D$6,$A11)+COUNTIFS($A$1:$A$6,B$10,$F$1:$F$6,$A11)+COUNTIFS($A$1:$A$6,B$10,$E$1:$E$6,$A11)+COUNTIFS($A$1:$A$6,B$10,$B$1:$B$6,$A11)
Actual data example: Example data.xls
