Calculate the Percentage of Specific Values in Excel

Use COUNTIF and COUNTA to find the Percentage of Yes/No Responses

Find the Percentage of Responses with COUNTIF and COUNTA
Find the Percentage of Responses with COUNTIF and COUNTA. © Ted French

COUNTIF and COUNTA Overview

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)

Note: 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.

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 in to E3, the result in E6 changes to 75%
  • If the answer No is entered in to 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.

    Format
    mla apa chicago
    Your Citation
    French, Ted. "Calculate the Percentage of Specific Values in Excel." ThoughtCo, Dec. 31, 2016, thoughtco.com/calculate-the-percentage-of-specific-values-3123726. French, Ted. (2016, December 31). Calculate the Percentage of Specific Values in Excel. Retrieved from https://www.thoughtco.com/calculate-the-percentage-of-specific-values-3123726 French, Ted. "Calculate the Percentage of Specific Values in Excel." ThoughtCo. https://www.thoughtco.com/calculate-the-percentage-of-specific-values-3123726 (accessed May 21, 2018).