How to Count Yes, No and Ticks in Excel

To count Yes in Excel, use COUNTIF with the word in double quotes: =COUNTIF(B2:B8,"Yes"). The same formula counts No, Pending or any other answer, and with a small change it also counts ticked checkboxes and tick symbols. This guide covers each case, plus percentages and the spacing problems that make a Yes count come out wrong.

How to count Yes answers#

COUNTIF counts the cells in a range that match one condition. For Yes answers, the condition is the word "Yes":

=COUNTIF(range, "Yes")

Every example uses this RSVP list in A1:B8:

1. Click an empty cell, such as D2.
2. Type =COUNTIF(B2:B8,"Yes").
3. Press Enter.

The result is 4: Ana, Cara, Eve and Gia. Eve typed "yes" in lower case, but COUNTIF ignores capitals, so she counts. The quotes are required, because Yes is text. For more about COUNTIF criteria, see how to use COUNTIF.

How to count No answers and blanks#

Change the criteria to count the other answers:

"" means empty, so it finds Dan's blank reply. "<>" means "not empty", which counts everyone who replied; COUNTA gives the same 6. Note that "<>Yes" includes the blank cell as well as the two No answers. The guide to COUNTIF not blank explains the blank and non-blank criteria in more detail.

A quick check: Yes + No + blank should equal the number of guests. Here 4 + 2 + 1 = 7. If the numbers don't add up, some cells hold a different word, like "Maybe", or a misspelling.

How to calculate the percentage of Yes#

Divide the Yes count by a total, then format the cell as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%). Which total you use changes the answer:

The first divides 4 by the 6 people who replied. The second divides 4 by all 7 guests, treating Dan's missing reply as not Yes. Say clearly which one you report, because they answer different questions.

How to count ticked checkboxes#

A checkbox in Excel stores a logical value behind it: TRUE when it is ticked and FALSE when it isn't. In Microsoft 365 you can add checkboxes straight into cells with Insert > Checkbox. The cell itself then holds TRUE or FALSE. Older form-control checkboxes store the value in a linked cell instead, which you choose in Format Control.

Either way, count the ticks by counting TRUE, with no quotes:

=COUNTIF(B2:B8,TRUE)

Use FALSE to count the unticked boxes. Don't put TRUE in quotes: "TRUE" looks for the text TRUE, which is not the same as the logical value a checkbox stores.

How to count tick symbols#

If the ticks are characters typed into cells, count the character itself. For a Unicode tick mark:

=COUNTIF(B2:B8,"✓")

Many older sheets make a tick by typing the letter ü and setting the font to Wingdings, which draws ü as a tick. The cell still holds ü, so count that letter: =COUNTIF(B2:B8,"ü"), or =COUNTIF(B2:B8,CHAR(252)) if you can't type it easily. To find out which character a tick really is, click the cell and look at the formula bar, or use =UNICODE(B2), which returns 10003 for ✓ and 252 for ü.

Why a Yes count comes out too low#

When COUNTIF misses some Yes answers, the data is usually not quite what it seems. Check these:

1. Extra spaces. "Yes " with a trailing space doesn't match "Yes". Test with =COUNTIF(B2:B8,"*yes*"); if that is higher, spaces or extra words are the cause. Clean the column with TRIM.
2. Different words. "Y", "Yep" or "Confirmed" won't match "Yes". Decide on one answer and fix the others.
3. Wrong range. Make sure the range covers every row, including any added at the bottom.
4. Checkbox values counted as text. Use TRUE without quotes.

The best fix is to stop the problem at the source with a drop-down list. Select the answer cells, go to Data > Data Validation, choose List, and type Yes,No as the source. People then pick an answer instead of typing it, so every Yes is spelled the same way.

How to turn Yes and No into 1 and 0#

Sometimes you need Yes and No as numbers, for example to add them to a score or show them in a chart. A helper column does it. In C2, type this and fill down:

=IF(B2="Yes",1,0)

The SUM of that column equals the COUNTIF result, 4. To skip the helper column, use SUMPRODUCT in one cell:

=SUMPRODUCT(--(B2:B8="Yes"))

The comparison returns TRUE or FALSE for each row, -- turns them into 1s and 0s, and SUMPRODUCT adds them up, giving 4 again. This form is handy when the rule is more complex than COUNTIF allows, such as comparing two columns row by row.

Count Yes with a second condition#

To count Yes answers for one group only, use COUNTIFS. If column C held each guest's team, this counts the Yes replies from the Sales team:

=COUNTIFS(B2:B8,"Yes",C2:C8,"Sales")

A row counts only when both conditions are TRUE. The same idea lets you build a small table of Yes counts by team, with each team name in a cell and the formula filled down. For other counting jobs, see how to count cells with text, how to count unique values and how to count duplicates.

Practise it#

Counting answers with COUNTIF is a quick win in any screening test. Practise COUNTIF in a live spreadsheet: you type the criteria and the lesson checks it instantly. Keep the COUNTIF function guide nearby for the syntax, and try the RSVP exercise on this page first.