# Calculate Percentage with Criteria in Excel (2 Useful Examples)

## Method 1 – Use the IF Function to Calculate a Percentage with Criteria in Excel

Consider the following dataset with the sales volume of some employees for two consecutive months. We want to calculate the percentage of change in sales volume. If the change in sales volume is negative, we will assign the word “Decreased” instead of calculating the change.

• In cell E6, apply the following formula and press the Enter key.
`=IF(D6-C6>0,(D6-C6)/C6,"Decreased")`
• Set the Number Format of the cell to Percentage.
• Drag down the Fill Handle icon to copy the formula into the remaining cells.

## Method 2 – How to Calculate a Percentage Based on Cell Color in Excel

We have set colors for the cells that have a positive percentage change value and the cells with the word “Decreased” are ignored (you can achieve this manually or by using Conditional Formatting). We want to calculate the percentage of rows that have this color criteria.

• In cell C14, apply the following formula and press the Enter key to count total rows.
`=COUNTA(E6:E12)`

• Select any cell in the dataset.
• Go to the Data tab and click on the Filter button.
• When the filter dropdown icons are visible, click on the dropdown icon of the column where you want to apply the color criteria filter.

• In the dropdown menu, click on the Filter by Color option and then select the desired cell color.

• Only the rows that contain colored cells are visible.
• To calculate the count of only visible rows, apply the following formula in cell C15 and press the Enter key.
`=SUBTOTAL(3,E6:E12)`

• Apply the following formula in cell C16 and set the cell Format to Percentage.
`=C15/C14`
• You will have your desired output.

## Calculate Percentage with Criteria in Excel: Knowledge Hub

Get FREE Advanced Excel Exercises with Solutions!
Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF