Sometimes, in our **Excel **worksheet, we need to calculate the percentage based on cell color. In this article, we’ll show you the simple methods to **Calculate **the** Percentage **in **Excel **based on** Cell Color**.

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the **Salesman**, **Product**, and **Net Sales **of a company. In this case, we have colored the net sales of some salesman and we’ll show you how to find out the percentage of the colored net sales to the total net sales.

**Table of Contents**hide

## Download Practice Workbook

To practice by yourself, download the following workbook.

## What is Percentage?

A number or ratio expressed as a fraction of **100 **is known as a **Percentage**. The sign of percentage is ‘**%**’. The basic percentage is calculated by the formula:

**Percentage = (Part / Whole)*100**

## 4 Methods to Calculate Percentage in Excel Based on Cell Color

### 1. Excel SUBTOTAL Function to Calculate Percentage Based on Cell Color

We know, **Excel **provides various **Functions **and we use them to perform many operations. At first, we’ll use the **Excel SUBTOTAL** function for computing the percentage based on the cell color along with the **Filter **feature. The **SUBTOTAL** function performs a particular task based on the function number applied in the argument. Therefore, follow the steps below to know how to do the task.

**STEPS:**

- Firstly, select cell
**D12**and type the formula:

`=SUBTOTAL(9,D5:D10)`

Here, in the argument, **9 **is the function number for **SUM **and** D5:D10** is the range of cells.

- Then, press
**Enter**and it’ll return the sum result. - After that, copy the result and paste it as values in cell
**F4**like it’s shown in the image below.

- Now, select the range of cells you want to work with.

- Then, select
**Filter**from the**Sort & Filter**drop-down in the**Editing**group under the**Home**tab.

- After that, select the drop-down icon beside the
**Net Sales**. - And then, select the
**Green**colored box in**Filter by Cell Color**options like it’s shown in the following picture.

- Consequently, you’ll see the
**Green**colored cells only. - Additionally, the sum result in cell
**D12**will get updated automatically and now it’ll return the sum of those green-colored cells only.

- And then, select cell
**D13**and type the formula:

`=D12/F4`

- After that, press
**Enter**.

- Next, select the ‘
**%**’ icon in the**Number**group under the**Home**tab.

- Finally, you’ll get the desired percentage value of green-colored cells.

**Related Content:** **How to Calculate Percentage Based on Conditional Formatting (6 Ways)**

### 2. Apply SUMIF Function for Calculating Percentage Based on Cell Color

Secondly, we’ll use another **Excel **function to calculate the percentage based on cell color. The **Excel SUMIF** function computes the sum of a range of cells based on a specific condition given in the argument. In this method, we’ll use the **SUMIF** function to find out the percentage. So, follow the steps to perform the task.

**STEPS:**

- At first, select
**Define Name**from**Define Names**drop-down under the**Formulas**tab.

- As a result, a dialogue box will pop out. There, type
**ColoredNumber**in the**Name**section. - Then, in the
**Refers to**section, type the formula:

`=GET.CELL(38,‘sumif’!$D5)`

- After that, press
**OK**.

Here, the **GET.CELL **function takes **38 **to return the code color, **sumif **is the name of the worksheet and **D5 **is the cell reference.

- Subsequently, a
**Name Manager**dialogue box will pop out and there press**Close**.

- Now, select cell
**E5**and type:

`=ColoredNumber`

- Then, press
**Enter**and use the**AutoFill**tool to fill the series. - Consequently, it’ll return the color codes.

- Next, select cell
**D12**. Here, type the formula:

`=SUM(D5:D10)`

- Then, press
**Enter**. - After that, select cell
**D13**. There, type the formula:

`=SUMIF(E5:E10,E8,D5:D10)`

Here, **E5:E10** is the criteria range, **E8 **is our criteria and **D5:D10** is the sum range.

- Now, press
**Enter**. - And then, select cell
**D14**and type the formula:

`=D13/D12`

- Afterward, press
**Enter**and select the ‘**%**’ icon in the**Number**group under the**Home**tab.

- At last, the accurate percentage result will appear in cell
**D14**.

**Related Content: Percentage Formula in Excel (6 Examples)**

**Similar Readings:**

**Percentage Difference between Two Percentages Excel (2 Easy Ways)****How to Calculate Percentage of Sales in Excel (5 Suitable Methods)****Subtract a Percentage in Excel (Easy Way)****Calculate Year over Year Percentage Change in Excel (3 Easy Techniques)****How do you Calculate Percentage Increase or Decrease in Excel**

### 3. Cell Color Based Percentage Calculation with Excel SUMIFS Function

Moreover, we can use the **Excel SUMIFS** function for percentage calculation based on cell color. Hence, learn the process to compute the percentage.

**STEPS:**

- In the beginning, select
**Define Name**from**Define Names**drop-down under the**Formulas**tab.

- Consequently, a dialogue box will pop out. Here, in the
**Name**section, type**ColorCode**. - Then, in the
**Refers to**section, type the formula:

`=GET.CELL(38,sumifs!$D5)`

- After that, press
**OK**.

Here, the **GET.CELL** function takes **38 **to return the code color, the name of the worksheet is **sumifs **and **Cell D5 **is the cell reference.

- Subsequently, a
**Name Manager**dialogue box will pop out and there press**Close**.

- Now, select cell
**E5**and type:

`=ColorCode`

- Then, press
**Enter**and use the**AutoFill**tool to fill the series.

- Next, select cell
**D12**. Here, type the formula:

`=SUM(D5:D10)`

- Then, press
**Enter**. - After that, select cell
**D13**. There, type the formula:

`=SUMIFS(D5:D10,E5:E10,E6)`

Here, **D5:D10** is the sum range, **E5:E10** is the criteria range and **E6 **is the criteria.

- Now, press
**Enter**. - And then, select cell
**D14**and type the formula:

`=D13/D12`

- Afterward, press
**Enter**and select the ‘**%**’ icon in the**Number**group under the**Home**tab.

- Eventually, the precise percentage value will appear in cell
**D14**.

**Related Content: Calculate Average Percentage in Excel [Free Template+Calculator]**

### 4. Percentage Calculation Based on Cell Color with Excel VBA

Lastly, we’ll apply the **Excel VBA** code to get our percentage based on cell color. Therefore, follow along to learn the process.

**STEPS:**

- At first, select
**Visual Basic**under the**Developer**tab.

- Immediately, a new window will pop out.
- Then, select
**Module**under the**Insert**tab.

- As a result, another window will pop out. There, paste the code given below:

```
Function ColorTotal(CClr As Range, rRng As Range)
Dim sum As Long
Dim color As Integer
color = CClr.Interior.ColorIndex
For Each cl In rRng
If cl.Interior.ColorIndex = color Then
sum = WorksheetFunction.sum(cl, sum)
End If
Next cl
ColorTotal = sum
End Function
```

- After that, close the
**Visual Basic**window. - Now, select cell
**D12**and type the formula:

`=SUM(D5:D11)`

- Then, press
**Enter**. - Again, select cell
**D13**and type the formula:

`=ColorTotal(D5,D5:D10)`

- Next, press
**Enter**. - After that, select cell
**D14**and type the formula:

`=D13/D12`

- Then, press
**Enter**and select the ‘**%**’ icon in the**Number**group under the**Home**tab.

- Lastly, the percentage of the colored cells to the total net sales will appear in cell
**D14**.

**Read More: Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)**

## Conclusion

Henceforth, you will be able to **Calculate the Percentage **in **Excel **based on **Cell Color** with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.