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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62K Get Help
- 337 Global Discussions
- 197 Industry Talk
- 427 Announcements
- 4.3K Ideas & Feature Requests
- 130 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 450 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!