4 Easy Ways to Calculate Percentage in Excel Based on Cell Color

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.

Dataset for the product order sheet.

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.

Using the SUBTOTAL function

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

Sort & Filter for a selected range

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

Use of Filter by color

  • Now we get the Total Cost of the colored item.

the sum of total colored item cost

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

Determining cell color percentage

πŸ“• 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.

Creating a color code function

  • Now we select the Close option from the dialogue box.

selecting the close button

  • After that, we write this function in cell F6.

=Colorcode

Applying the Colorcode function

  • Following that we use the Fill Handle to drag the formula.

Use of the Fill Handle tool

  • 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.

Using the sumif function

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

Determining the cell color percentage

πŸ“• 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.

Use of the SUMIFS function

πŸ“• 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.

Applying the Visual Basic

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

Inserting into 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

Code for percentage based on cell color.

  • 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.

Use of the Sumcolor function.

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

Determining the cell color percentage

πŸ“• 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.

Using the conditional formatting

  • 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.

Applying the ISFORMULA function

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

Selecting the Fill color.

  • Now we get this result with cell color.

total colored cell

πŸ“• 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.

Using the SUBTOTAL function

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

Applying the Sort & 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.

Filter by color

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

Number of colored cells


πŸ“„ 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

(Visited 39 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo