Countifs for a range based on checked box in a different column

Options

I am working on a training matrix where our team tracks the operator's training status for multiple assays. We also have a columns for different suites of trainings needed per job title where if the assay is needed for the job title, the box is checked.

The ultimate request is to show % Complete Trained based on the different training suites. I made a fake sheet with 4 operators and one suite to test things out, but I am running into issues counting the number of "trained" assays based on the checked box criteria. I have tried this:

=COUNTIFS([Operator 1]:[Operator 4], "Trained", [Training Suite 1]@row, 1) (getting #UNPARSEABLE on this)

Also tried:

=COUNTIFS([Operator 1]:[Operator 4], "Trained", [Training Suite 1]:[Training Suite 1], 1) (getting #INCORRECT ARGUMENT SET on this)

In this fake sheet, I would expect to get 12 as the result counting the 'Trained' assays where the box is checked.

Thanks.

Tags:

Best Answer

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭
    Answer ✓
    Options

    @Kari Natvig just need to change the formula a bit. Reference each column with the countif formula then add together.

    =COUNTIFS([Operator 1]:[Operator 1], "Trained", [Training Suite 1]:[Training Suite 1], 1) + COUNTIFS([Operator 2]:[Operator 2], "Trained", [Training Suite 1]:[Training Suite 1], 1) + COUNTIFS([Operator 3]:[Operator 3], "Trained", [Training Suite 1]:[Training Suite 1], 1) + COUNTIFS([Operator 4]:[Operator 4], "Trained", [Training Suite 1]:[Training Suite 1], 1)

    I updated the sheet so you can see.

Answers

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭
    Answer ✓
    Options

    @Kari Natvig just need to change the formula a bit. Reference each column with the countif formula then add together.

    =COUNTIFS([Operator 1]:[Operator 1], "Trained", [Training Suite 1]:[Training Suite 1], 1) + COUNTIFS([Operator 2]:[Operator 2], "Trained", [Training Suite 1]:[Training Suite 1], 1) + COUNTIFS([Operator 3]:[Operator 3], "Trained", [Training Suite 1]:[Training Suite 1], 1) + COUNTIFS([Operator 4]:[Operator 4], "Trained", [Training Suite 1]:[Training Suite 1], 1)

    I updated the sheet so you can see.

  • Kari Natvig
    Options

    Thanks! Although some of my formulas will be quite large, this will work.

    Kari

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!