Calculate the Percentage of Specific Values in Excel

Use COUNTIF & COUNTA to find the percentage of yes/no responses

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on November 14, 2019

Excel's COUNTIF and COUNTA functions can be combined to find the percentage of a specific value in a range of data. This value can be text, numbers, Boolean values or any other type of data.

The example below combines the two functions to calculate the percentage of Yes/No responses in a range of data.

The formula used to accomplish this task is: 

=COUNTIF(E2:E5,"Yes")/COUNTA(E2:E5)

Quotation marks surround the word "Yes" in the formula. All text values must be contained within quotation marks when entered into an Excel formula.

In the example, the COUNTIF function counts the number of times the desired data — the answer Yes — is found in the selected group of cells.

COUNTA counts the total number of cells in the same range that contain data, ignoring any blank cells.

Example: Finding the Percentage of Yes Votes

As mentioned above, this example finds the percentage of "Yes" responses in a list that also contains "No" responses and a blank cell.

Find the Percentage of Responses with COUNTIF and COUNTA
Ted French

Entering the COUNTIF — COUNTA Formula

  1. Click on cell E6 to make it the active cell;
  2. Type in the formula: = COUNTIF( E2:E5, "Yes" )/COUNTA( E2:E5 );
  3. Press the Enter key on the keyboard to complete the formula;
  4. The answer 67% should appear in cell E6.

Since only three of the four cells in the range contain data, the formula calculates the percentage of yes responses out of three.

Two out of three responses are yes, which is equal to 67%.

Modifying the Percentage of Yes Responses

Adding a yes or no response to cell E3, which was initially left blank, will modify the result in cell E6.

  • If the answer Yes is entered into E3, the result in E6 changes to 75%
  • If the answer No is entered into E3, the result in E6 changes to 50%

Finding Other Values With This Formula

This same formula can be used to find the percentage of any value in a range of data. To do so, substitute the value sought for "Yes" in the COUNTIF function. Remember, non-text values do not need to be surrounded by quotation marks.

Was this page helpful?