COUNTIF function for the checkbox?

Matt Welton

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

Aravind GP

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!

Aravind GP

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!

Matt Welton

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!

Aravind GP

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.

Matt Welton

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

Matt Welton

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

Aravind GP

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.