COUNTIF function for the checkbox?
Hello all! I want to create a graph with checkboxes.
Lets say I have some departments of:
Governance / PMO
M&A
Communication
Commercial
and in my sheets I also have a checkbox column which is ticked based on whether they need support on a project.
I am trying to figure out how to use the countif function to count the whether each department has the box ticked or not. Is this possible or am I using the wrong function?
Thank you
Best Answer

Hi Matt,
I assume you have the departments as checkbox columns, in which case it should be easier to use the COUNTIF function. The formula could be something like "=COUNTIF([Governance / PMO]:[Governance / PMO], 1)". Using this formula you can get a count of rows where the checkbox under the column Governance/PMO is checked.
If you have a separate checkbox column and possibly a single select dropdown column specifying the departments, then you can use COUNTIFS. The formula could be something like "=COUNTIFS([Department]:[Department], "Governance / PMO", [Checkbox]:[Checkbox], 1)". I am assuming that the column name containing the departments is "Department" and the checkbox column name is "Checkbox". It will be more helpful if you can share some screenshots for me to give you the actual formula.
Hope this helps!
Thanks,
Aravind
Associate Director
Copernicus Consulting Pte. Ltd.
P: +65 9230 5657  E: aravind@copernicusworld.com
Feel free to reach out for licenses, services, and training on Smartsheet
Answers

Hi Matt,
I assume you have the departments as checkbox columns, in which case it should be easier to use the COUNTIF function. The formula could be something like "=COUNTIF([Governance / PMO]:[Governance / PMO], 1)". Using this formula you can get a count of rows where the checkbox under the column Governance/PMO is checked.
If you have a separate checkbox column and possibly a single select dropdown column specifying the departments, then you can use COUNTIFS. The formula could be something like "=COUNTIFS([Department]:[Department], "Governance / PMO", [Checkbox]:[Checkbox], 1)". I am assuming that the column name containing the departments is "Department" and the checkbox column name is "Checkbox". It will be more helpful if you can share some screenshots for me to give you the actual formula.
Hope this helps!
Thanks,
Aravind
Associate Director
Copernicus Consulting Pte. Ltd.
P: +65 9230 5657  E: aravind@copernicusworld.com
Feel free to reach out for licenses, services, and training on Smartsheet

I made a test version. So I have been making a separate sheet for the formulas but theoretically I would want to have which departments have checked the support required checkbox. Thank you Aravind!

Hi Matt,
So in this case, you can use the COUNTIFS formula. For the sheet shared, you can use the formula in the same sheet under Sheet Summary or a different metric sheet. If you use the same sheet, the formula for Communication department support will be "=COUNTIFS([Department]:[Department], "Communication", [Support Required]:[Support Requited], 1)". You can use the same formula for all departments by replacing the word "Communication" with the other departments one by one to get the count for each department.
If you are using a separate metrics sheet, you can reference this sheet and select the Department column as the first range and the support required column as the second range.
Thanks,
Aravind
Associate Director
Copernicus Consulting Pte. Ltd.
P: +65 9230 5657  E: aravind@copernicusworld.com
Feel free to reach out for licenses, services, and training on Smartsheet

Thank you. That is great. Cheers for all the help. :)

Hey Apologies to ask again. But how would I do this formula in another sheet and referencing the data?

Hi Matt,
As mentioned in my previous response. If you are using a separate metrics sheet, you can reference this sheet and select the Department column as the first range and the support required column as the second range.
So, do the equals and COUNTIFS and before selecting the columns, click on the "Reference another sheet", then you can select the sheet followed by the columns.
Thanks,
Aravind
Associate Director
Copernicus Consulting Pte. Ltd.
P: +65 9230 5657  E: aravind@copernicusworld.com
Feel free to reach out for licenses, services, and training on Smartsheet
Help Article Resources
Categories
Check out the Formula Handbook template!