Subscribe

Lesson 100 of 104 5 min Updated Completed

How to Count Yes in Excel With COUNTIF

Survey data often has simple Yes / No answers, and a common question is: how many said Yes? The COUNTIF function answers it, whether the answers are typed as text or shown as ticked checkboxes.

The COUNTIF function

COUNTIF counts the cells in a range that meet a condition:

=COUNTIF(range, criteria)

  • range: the cells to check.
  • criteria: what a cell must match to be counted.

1. When Yes is typed as text

This list shows whether each store has an item: Yes, No, or Maybe (sold seasonally).

A list of stores with Yes, No and Maybe

To count the Yes answers in B2:B9, type this in E3:

=COUNTIF(B2:B9,"Yes")

COUNTIF counting the Yes answers

Note: COUNTIF is not case-sensitive, so it counts Yes, yes and YES alike.

To count the No answers, change the criteria to "No":

COUNTIF counting the No answers

Use a cell as the criteria

Instead of typing “Yes” in the formula, you can point to a cell that contains it, such as D3:

=COUNTIF($B$2:$B$9,D3)

COUNTIF using cell D3 as the criteria

Now you can drag the fill handle down to E4, and the formula uses D4 (No) automatically, with no retyping. The $ signs keep the range fixed as you copy.

The formula copied down to count No

2. When Yes is a ticked checkbox

Sometimes the answers are checkboxes: ticked means Yes, empty means No.

A list of stores with checkboxes

COUNTIF can’t read a checkbox directly, so first link each checkbox to a helper cell. A linked cell shows TRUE when the box is ticked and FALSE when it isn’t.

  1. Right-click the column C heading and click Insert to add a helper column next to the checkboxes.

    Inserting a helper column C

  2. Right-click the first checkbox and click Format Control.

    Format Control in the checkbox's right-click menu

  3. Open the Control tab.

    The Control tab of the Format Control dialog box

  4. Click the button at the right of the Cell link box.

    The Cell link box

  5. Click cell C2, the helper cell for this checkbox.

    Linking the checkbox to C2

  6. Click OK. C2 now shows TRUE. Repeat for each checkbox, linking each one to the cell next to it.

    TRUE and FALSE values in the helper column

  7. In F3, count the ticked boxes: =COUNTIF(C2:C9,TRUE).

    COUNTIF counting TRUE values

  8. In F4, count the empty boxes: =COUNTIF(C2:C9,FALSE).

    COUNTIF counting FALSE values

Tip: For the percentage of Yes answers, divide by the number of answers: =COUNTIF(B2:B9,"Yes")/COUNTA(B2:B9), then click the % button. See How to Calculate Percentage.

Using Microsoft 365? The newer Insert > Checkbox puts the checkbox inside the cell itself, so the cell is already TRUE or FALSE. Just use =COUNTIF(B2:B9,TRUE), with no helper column needed.