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 countIf function, I'm able to see the total amount that one person is planned to train, but i'd like to be able to differentiate between the different types of training. How am I able to do that?

 

 

Sincerely,

Rowan

 

excel
Formulas and Functions

3 Replies

Sort By
  • 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)
)

     

     

  • rachel's avatar
    rachel
    Steel Contributor
    Nov 03, 2023
    Hi,

    COUNTIFS function can handle multiple criterion.
    e.g you can use this formula to get "How often Boat" for Person A:
    =COUNTIFS(C3:C11, "Boat",D3:D11,"*A*")

Resources