Forum Discussion

Rowan950's avatar
Rowan950
Copper Contributor
Nov 02, 2023

CountIf multiple variables

Hello everyone,    I'm a coach of a first year sporting team. I wanna be able to quickly see how often everyone is training and what kind of training it is (fixed amount of options). Using the coun...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 03, 2023

Rowan950 

If you're using 365, you could use this which will tally the results and add all necessary labels and detail:

 

=LET(
    person, SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(", ", 1, Table1[Who?]), , ", "))),
    categories, TOROW(SORT(UNIQUE(FILTER(Table1[Training], Table1[Who?] <> "")))),
    r, COLUMNS(categories),
    header, HSTACK("Who?", "How often?", categories),
    tally, COUNTIFS(Table1[Training], categories, Table1[Who?], "*" & person & "*"),
    total, MMULT(tally, SEQUENCE(r, , 1, 0)),
    summary, HSTACK(person, total, tally),
    VSTACK(header, summary)
)

 

 

Resources