How to Calculate the Percentage Based on Different Data Type and Table Structure in Excel

We often do statistics analysis by Excel in our daily work, and sometimes we need to count the percentage for different data in a series of data, so that we can see the data distribution of this series of data and we can also find out which data accounts the largest proportion. Due to different data type and table structures, we will use different methods to calculate the percentage. So, in this article, we will provide you some methods to calculate percentage based on different situations. You can use the formula including COUNTIF and COUNTA functions to calculate percentage, or use SUM function, or insert a PivotTable per your demands. Please continue to read this article, select one proper method to solve your problem.

Method 1: Count the Percentage by COUNTIF and COUNTA Functions in Excel


See the example below. We do a simple statistic for fruit category on weekdays.

If we want to know which fruit is the most common occurs for a week, you can count the percentage for each of them. Let’s follow below steps to learn how to count the percentage.

Step 1: Create a table like below. D column lists all fruit categories. E column lists the percentage for each fruit.

Calculate the Percentage 2

Step 2: In E2, enter the formula =COUNTIF($B$2:$B$11,D2)/COUNTA($B$2:$B$11). In this formula, COUNTIF counts the number for D2 (Apple) in the absolute range $B$2:$B$11, COUNTA counts all cells number in the absolute range $B$2:$B$11. Then use COUNTIF divide COUNTA we can get the percentage.

Calculate the Percentage 3

Step 3: Click Enter to get the result.

Calculate the Percentage 4

Step 4: Drag the fill handle to fill the following cells. Verify that all percentages are displayed.

Calculate the Percentage 5

Notes:

  1. This method is typically used to count the percentage of the times a value appears in a set of data.
  2. This method doesn’t take effective if the data is a number. So, we need another method to calculate the percentage for a number among a batch of numbers in Excel.

Method 2: Calculate the Percentage by SUM Function in Excel


See the example below.

Calculate the Percentage 6

If we use above method 1 to calculate percentage of the sales for product A, we will get an improper value. See screenshot below:

Calculate the Percentage 7

In this case we can get the percentage by ‘Sales of each product/Total Sales’, so we can use a simple formula to calculate the percentage.

Step 1: In E2, enter the formula =B2/SUM($B$2:$B$5).

Calculate the Percentage 8

Step 2: Click Enter to get the result.

Calculate the Percentage 9

Step 3: Drag the fill handle to fill the following cells. Verify that percentage is displayed for each product.

Calculate the Percentage 10

Method 3: Calculate the Percentage by Insert a PivotTable in Excel


See the table below. It is a little more complex than above two tables. Products are duplicated displayed in the list. To calculate the percentage for each product, we can use a PivotTable.

Calculate the Percentage 11

Step 1: Click Insert->Recommended PivotTables.

Calculate the Percentage 12

Step 2: In the pops up Create PivotTable, check on ‘Select a table or range’, click the arrow button and select the range you want to count the percentage on sheet1. In this case, you can select $A$1:$C$17. Then click OK.

Calculate the Percentage 13

Step 3: Verify that Recommended PivotTables window pops up. Click OK here.

Calculate the Percentage 14

Step 4: Verify that PivotTable is created on new sheet2.

Calculate the Percentage 15

Step 5: Double click on ‘Sum of Sales’ to load ‘Value Field Settings’.

Calculate the Percentage 16

Step 6: Click on ‘Show Values As’ tab. In ‘Show Values As’ dropdown list, click the arrow button, and select the third one ‘% of Column Total’.

Calculate the Percentage 17

Step 7: Update the Custom Name to ‘Percentage of Sales’, then click OK.

Calculate the Percentage 18

Step 8: Verify that percentages are calculated properly for all products. You can also directly click on A3 to update the Row Labels per your requirement.

Calculate the Percentage 19

Related Functions


  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel COUNTA function
    The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…

 

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

Sidebar