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)