COUNTIF, one cell is text, and one cell is 'check box'

I have a summary formula that works to count a cell if there is text in it, works good, can weed out the Data that I don't want to count with other summary formulas. However there is often preplanning or left over data. My solution is to show the [route] completed [done].
=countif([done]:[done],1) works
=countif([route]:[route],is text(@cell)) works
But how do I combine these so the route is only counted if done is checked, so in the pic below, only the routes with the check boxed will be counted.
=IF(DONE@row, Route@row, "CANX") is being used as a helper to feed another sheet but I cant figure out how to view when there is all this happening... maybe I should just use my totals sheet?
Best Answer
-
Hey @SkiPatrolScott
@Nick Korna has got you covered!
The COUNTIF formula only allows one set of criteria to be used, which is why it wouldn't allow you to combine your two singular formulas. My personal preference is to never use the COUNTIF (singular) because of this criteria restriction and to always use a COUNTIFS (or SUMIFS vs SUMIF) because with these plural functions there are no limitations - you can use them with any number of criteria, from one to however many. But that is a personal preference. 😉
As always, the community is here whenever you have a question.
Kelly
Answers
-
Hi @SkiPatrolScott,
You can just combine your 2 COUNTIF statements into one with COUNTIFS:
=COUNTIFS([route]:[route],ISTEXT(@cell),[done]:[done],1)
Hope this helps - if there is a problem or you've any questions then just ask! 😊
-
Hey @SkiPatrolScott
@Nick Korna has got you covered!
The COUNTIF formula only allows one set of criteria to be used, which is why it wouldn't allow you to combine your two singular formulas. My personal preference is to never use the COUNTIF (singular) because of this criteria restriction and to always use a COUNTIFS (or SUMIFS vs SUMIF) because with these plural functions there are no limitations - you can use them with any number of criteria, from one to however many. But that is a personal preference. 😉
As always, the community is here whenever you have a question.
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!