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

Kari Natvig

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.

https://app.smartsheet.com/b/publish?EQBCT=41c9d9891ebb4322b19e409397484216

Thanks.

Lindsay AR

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

Lindsay AR

@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

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

Kari