ExcelInsider Team
People who use Google Sheets regularly often need to know how many times a certain condition is met in a group of cells. When every value in that range is Boolean (TRUE or FALSE), the most logical thing to do is to count the number of cells and return TRUE. This means that a certain condition or formula has been met. Users often use COUNTIF for task tracking, conditional logic, checklist completion, or data validation reports, where TRUE means success or completion.
To find out how many cells in Google Sheets are TRUE, do the following:
➤ Select cell F2 to display the count.
➤ Enter the formula:
=COUNTIF(B2:D11, TRUE)
➤ Press Enter to get the count of all TRUE values.
In this article, we’ll look at a few different ways to count the number of TRUE values in Google Sheets using COUNTIF. This includes direct COUNTIF, ARRAYFORMULA, SUMPRODUCT, and other indirect ways. We’ll also answer common questions and explain when these formulas work best.
What Does It Mean to Use COUNTIF with TRUE in Google Sheets?
When we say use COUNTIF with TRUE, we mean the job of counting how many cells have the Boolean value TRUE in them. TRUE is a logical value that often happens when a condition is met, like when a checkbox is checked or a formula gives a valid result. The condition is different from text or numbers.
Google Sheets sees TRUE and FALSE as different from text and numbers. We use functions like COUNTIF or ARRAYFORMULA in a certain way to count how many times TRUE shows up.
Using COUNTIF to Count Boolean Value TRUE
One of the easiest ways to find out how many cells in a range contain the Boolean value TRUE is to use the COUNTIF function. This method works best when you’re dealing with logical values that show things like attendance, task completion, or status checks. It’s simple to use and counts TRUE values in a single column or range quickly.
We are using Boolean values to track the number of students present over three days in this dataset. The cell shows TRUE if a student has attended and FALSE if they haven’t. The COUNTIF function is the easiest and most direct way to determine the number of students present on a specific day.
Steps:
➤ Select the F2 cell where you want the attendance count, in this case, Aiden’s Overall Attendance.
➤ Enter the formula:
=COUNTIF(B2:D2, TRUE)
➤ Press Enter to get a count of the days attended.
Note:
Ensure that the column has real TRUE/FALSE values and not text that says TRUE.
Using ARRAYFORMULA with IF to Count TRUE Based on Condition
This method works well when your TRUE values depend on more than one thing. You can make a logical test inside ARRAYFORMULA as well as count the results instead of directly referencing a column.
This method is particularly effective for counting students who satisfy a condition that pertains to multiple columns, such as being present for at least two out of three days.
Steps:
➤ Select an empty cell, in this case F2.
➤ Enter the formula:
=COUNTIF(ARRAYFORMULA((B2:B11+C2:C11+D2:D11)>=2), TRUE)
➤ Press Enter to get the count of TRUE values that meet the condition.
Note:
In this method, TRUE means 1 and FALSE means 0. So, adding up the columns gives the total number of days present.
Using SUMPRODUCT to Count TRUE Without COUNTIF
You can also use SUMPRODUCT to count TRUE values because it sees TRUE as 1 and FALSE as 0. When working with arrays and more than one condition, this method is often more flexible.
We are going to use SUMPRODUCT to count how many students were there on a certain day or even add up days.
Steps:
➤ Select an empty cell, here F2.
➤ Use the formula:
=SUMPRODUCT(–(B2:B11=TRUE), –(C2:C11=TRUE))
➤ Replace the range with your TRUE/FALSE range and press Enter.
Note:
Use the double negative (–) to convert TRUE/FALSE values into 1/0, enabling SUMPRODUCT to perform arithmetic operations. SUMPRODUCT functions seamlessly across arrays without needing helper columns or array formulas.
Using FILTER with COUNTIF to Count TRUE
Occasionally, you might want to only count TRUE values if a specific condition is satisfied. In that case, you can filter the rows first and then use COUNTIF only on the filtered result. This method is useful for large data sets or when counting by name, group, or category.
We’re going to see how many times a certain student was in class on a certain day. This method gets rid of the need to scan the whole dataset.
Steps:
➤ Select the output cell, e.g., F2.
➤ Enter the following formula:
=COUNTIF(FILTER(C2:D11, A2:A11=”Ethan”), TRUE)
➤ Press Enter to get the count.
Note:
You can change the day, name, or other criteria with this method.
Using COUNTIF with INDIRECT for Flexible Range Counting
If you’re making a dynamic dashboard or letting users choose a column from a dropdown, INDIRECT lets you send the column name to COUNTIF in real-time.
Now, we intend to use a cell (like a dropdown) to dynamically point to a certain column of attendance data.
Steps:
➤ Assume cell D13 contains the text C2:C11 (representing Day 2’s range).
➤ Use the formula:
=COUNTIF(INDIRECT(D13), TRUE)
➤ This counts TRUE values based on the range specified in D13.
Note:
This is outstanding for making reports that let users pick a day to look at. Setting the ranges of the columns ahead of time ensures optimal performance.
Frequently Asked Questions
What happens if I use “=COUNTIF(range, “TRUE”)” instead of without quotes?
You can use “TRUE” in COUNTIF in Google Sheets, but it’s better to use the unquoted Boolean value TRUE. Quoted “TRUE” might only count cells that have the word TRUE in them, not the logical value.
Can I count TRUE values in a column of checkboxes?
Yes, you can. When you check a box, it returns TRUE, and when you uncheck it, it returns FALSE. You can use =COUNTIF(range, TRUE) right on the checkbox column.
Can I count both TRUE and FALSE values?
Yes, you can use two COUNTIF formulas:
➤ =COUNTIF(range, TRUE)
➤ =COUNTIF(range, FALSE)
This gives a different count for each logical value.
Why does COUNTIF not work with complex logical expressions?
COUNTIF only works with one condition at a time and doesn’t support array formulas by default. Use ARRAYFORMULA, FILTER, or SUMPRODUCT for more complicated conditions.
Concluding Words
To keep track of tasks, reports, or validation lists that meet certain conditions, you need to be able to count TRUE values in Google Sheets. Depending on how your data is set up and how complicated the logic is, each method like COUNTIF, SUMPRODUCT, or ARRAYFORMULA has its own pros and cons. Try them out and pick the one that works best for you!
