Countifs for a range based on checked box in a different column
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.
Best Answer

@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

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

Thanks! Although some of my formulas will be quite large, this will work.
Kari
Help Article Resources
Categories
Check out the Formula Handbook template!