Sometimes we have to calculate the percentage of a certain value from the total value. It can be a certain sell value of a product from all the products sold in a company. So it can come in handy if we can somehow differentiate a certain percentage of a value from the total value. If the sale of any particular product is lower than a certain percentage we can take necessary measures. So in this article, we are going to learn four different ways to calculate the percentage in Excel based on the cell color.

## ๐ **Download Excel File**

Download the practice file from here.

**Learn to Calculate Percentages in Excel Based on the Cell Color with These 4 Methods**

In this article, we will learn four different methods to calculate the percentage of a certain value from the total value. We will use Excel functions like **SUBTOTAL**, **SUMIF**, **SUMIFS** functions, and the **VBA** option. Letโs assume we have a product data-sheet. We are trying to know how much percentage is the costs of television, light bulb, and bed with respect to the total product costs. For our demonstration purpose, the sample dataset is as follows.

**method**

### 1. Applying SUBTOTAL Function

In this section, we will use the **SUBTOTAL** function to calculate the percentage in Excel based on cell color. So the steps are as follows.

**โฌ๏ธโฌ๏ธ**** STEPS ****โฌ๏ธโฌ๏ธ**

- Firstly we go to cell
**E13**and type this formula there.

**=SUBTOTAL(9,E6:E11)**

**๐จ**** Formula Breakdown**

๐ย Here** 9** basically means the function **SUM**.

๐ย **E6:E11** is the sum range.

- We get the
**Total**cost in cell**E13**.

- Now we select the data range.
- Following that, we go to
**Sort & Filter**option. - After that, we select the
**Filter**option.

- After that, we select the dropdown from
**Total**Cost. - We select the
**Filter by Color**option. - We select the fill color.

- Now we get the
**Total Cost**of the colored item.

- Following that we divide the cell
**E13**by**E16**to get the percentage. - Finally, we get the result in cell
**E14**.

**๐ Read More: ****3 Ways to Add Percentage to Price in Excel with Formula**

**method**

### 2. Using SUMIF Function

In this section, we will learn how to use the **SUMIF** function to determine the required percentage in excel based on the cell color. Therefore the steps are as follows.

**โฌ๏ธโฌ๏ธ**** STEPS ****โฌ๏ธโฌ๏ธ**

- Firstly we go to the
**Formulas**option. - Then we select the
**Name Manager**. - We provide a name in our name section.
- After that, we fill this function in
**Refers to**the box.

**=GET.CELL(38,sumif!$E6)**

**๐จ**** Formula Breakdown**

๐ย **38** gives us the color code.

๐ **ย sumif!$E6 **denotes the** E6** cell of data sheet **sumif**.

- Finally, we press
**OK**.

- Now we select the
**Close**option from the dialogue box.

- After that, we write this function in cell
**F6**.

**=Colorcode**

- Following that we use the
**Fill Handle**to drag the formula.

- Now we write this formula in cell
**F13**.

**=SUMIF(F6:F11,F7,E6:E11)**

**๐จ**** Formula Breakdown**

๐ย Here **F6:F11** is our criteria range.

๐ย **F7** is the particular criteria.

๐ย **E6:E11** denotes our sum range.

- Finally, we divide cell
**F13**by**F14**to get the result in cell**F15**.

**๐ Read More: ****Calculate Percentage in Excel Using Absolute Cell Reference**

**method**

### 3. Implying SUMIFS Function

In this section, we will learn to use the **SUMIFS** function to calculate the percentage in excel based on the cell color. Therefore the step-by-step procedures are as follows.

**โฌ๏ธโฌ๏ธ**** STEPS ****โฌ๏ธโฌ๏ธ**

- Just like the previous method first, we make the
**Color code**column. - Now we write this formula in the cell
**F13**formula box.

**=SUMIFS(E6:E11,F6:F11,F7)**

**๐จ**** Formula Breakdown**

๐ย Here** E6:E11** is our sum range.

๐ย **F6:F11** means the criteria range.

๐ย **F7** denotes the criteria.

- Finally, we divide cell
**F13**by**F14**to get the result in cell**F15**.

**๐ Read More: ****5 Easy Methods to Add 10 Percent to a Number in Excel**

**method**

### 4. Using VBA Macro

In this final section, we will use the Excel **VBA** to calculate the percentage in the Excel based on the cell color. So the steps are as follows.

**โฌ๏ธโฌ๏ธ**** STEPS ****โฌ๏ธโฌ๏ธ**

- Firstly we go to the
**Developer**option. - After that, we go to the
**Visual Basic**option.

- Following that, we go to the
**Insert**option. - Then we select the
**Module**.

- We will get a window. Now we write this script in the module.

```
Function Sumcolor(cellvalue As Range, Totalrange As Range)
Dim summation As Long
Dim colour As Integer
colour = cellvalue.Interior.ColorIndex
For Each cl In Totalrange
If cl.Interior.ColorIndex = colour Then
summation = WorksheetFunction.sum(cl, summation)
End If
Next cl
Sumcolor = summation
End Function
```

- Now we write this formula in cell
**E13**.

**=Sumcolor(E7,E6:E11)**

**๐จ**** Formula Breakdown**

๐ย Firstly here **E7** is the cell value.

๐ **ย E6:E11** sum range.

- Finally, we divide cell
**E13**by**E14**to get the result in cell**E15**.

**๐ Read More: ****3 Quick Ways to Add 20 Percent to a Price in Excel**

**How to Create Cell Color with Formula in Excel**

In this section, we will learn how to use **Conditional Formatting** for the Excel cell color formula. So the steps are as follows.

**โฌ๏ธโฌ๏ธ**** STEPS ****โฌ๏ธโฌ๏ธ**

- Firstly we select the data range.
- After that, we go to
**Conditional Formatting**. - Then we select the
**New Rule**.

- After that, we select the Rule Type.
- Now we write this formula in the formula box.

**=ISFORMULA($E$6:$E$11)**

- Here
**ISFORMULA**returns us the result of cells containing formulas. - Now we select the
**Format**option.

- Now we go to the
**Fill**option. - After that, we select the background color.
- Finally, we press
**OK**.

- Now we get this result with cell color.

**๐ Read More: ****Calculate Percentage in Excel Based on Conditional Formatting**

**How to Count Colored Cells in Excel Using Formula**

In this section, we will learn how to count colored cells in Excel using formulas. So the steps are as follows.

**โฌ๏ธโฌ๏ธ**** STEPS ****โฌ๏ธโฌ๏ธ**

- Firstly we write this formula in cell
**E13**.

**=SUBTOTAL(3,E6:E11)**

**๐จ**** Formula Breakdown**

๐ย Here** 3 **denotes the **COUNTA** function.

๐ย **E6:E11** is our reference range.

- Following that we select our data range.
- Then we go to the
**Sort & Filter**option. - Then we select the
**Filter**option.

- Now we select the dropdown bar from the right.
- After that, we select the
**Filter by Color**option. - Then we select the
**Fill**color.

- Finally, we get the number of colored cells in cell
**E13**.

**๐**** Important Notes**

๐๏ธย When we use the function, we must be careful about the function argument.

๐๏ธย While using the **VBA** we have to save the workbook as macro enabled workbook.

**๐**** Takeaways from This Article**

๐ย We can use the **SUBTOTAL** function to calculate the percentage in the Excel based on cell color.

๐ย **SUMIF** function can be another way to calculate the percentage in Excel based on cell color.

๐ย If we want we can imply the **SUMIFS** function to do the same thing.

๐ย Also **VBA **is another efficient way to determine the percentage in Excel based on the cell color.

**Conclusion**

In this article, we have learned four different methods to calculate the percentage of a certain value from the total value. We can use Excel functions like **SUBTOTAL**, **SUMIF**, **SUMIFS,** etc. If we want we can also use the **VBA** in Excel. Users can use the functions or the **VBA** whichever they want. If you possess any queries regarding this article you can put your comment in the comment box. The writer will try his best to come up with a solution. For further queries, you can visit our website **www.Excelden.com**.

## Related Articles

**3 Easy Ways to Calculate Accuracy Percentage in Excel****4 Simple Ways to Calculate Forecast Accuracy Percentage in Excel****Calculate Percentage Change with Negative Numbers in Excel****6 Ways to Forecast Growth Rate in Excel****Calculate Profit and Loss Percentage with Formula in Excel**