How to Calculate Percentage in Excel Based on Cell Color (4 Methods)

To illustrate our methods for calculating percentage based on cell color, we’ll use the following sample dataset representing the Salesman, Product, and Net Sales of a company. We have colored the net sales of some salesman. Let’s calculate the percentage of the total net sales that the colored net sales comprise.

What is Percentage?

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

Percentage = (Part / Whole)*100

Calculating Percentage Based on Cell Color

Method 1 – Using the SUBTOTAL Function

We’ll use the Excel SUBTOTAL function along with the Filter feature here.

STEPS:

Select cell D12 and enter the formula:

=SUBTOTAL(9,D5:D10)

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

Press Enter to return the sum result.
Copy the result and paste it as a value in cell F4.

Select the range of cells you want to work with.

Select Filter from the Sort & Filter drop-down in the Editing group under the Home tab.

Select the drop-down icon beside Net Sales.
Select the Green colored box in the Filter by Cell Color options.

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.

Select cell D13 and enter the formula:

=D12/F4

Press Enter.

Select the ‘%’ icon in the Number group under the Home tab.

The desired percentage value of green-colored cells will be returned.

Method 2 – Using the SUMIF Function

The SUMIF function computes the sum of a range of cells based on a specific condition given in the argument.

STEPS:

Select Define Name from the Define Names drop-down under the Formulas tab.

A dialog box will pop out.

There, enter ColoredNumber in the Name section.
In the Refers to section, enter the formula:

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

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

A Name Manager dialog box will pop out

Click Close.

Select cell E5 and enter:

=ColoredNumber

Press Enter and use the AutoFill tool to fill the series.

The color codes will be returned.

Select cell D12 and enter the formula:

=SUM(D5:D10)

Press Enter.
Select cell D13 and enter 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.

Press Enter.
Select cell D14 and type the formula:

=D13/D12

Press Enter and select the ‘%’ icon in the Number group under the Home tab.

The accurate percentage result will appear in cell D14.

Method 3 – Using the Excel SUMIFS Function

STEPS:

Select Define Name from the Define Names drop-down under the Formulas tab.

A dialog box will pop out.

In the Name section, enter ColorCode.
In the Refers to section, enter the formula:

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

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.

A Name Manager dialog box will pop out.

Click Close.

Select cell E5 and enter:

=ColorCode

Press Enter and use the AutoFill tool to fill the series.

Select cell D12 and enter the formula:

=SUM(D5:D10)

Press Enter.
Select cell D13 and enter 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.

Press Enter.
Select cell D14 and type the formula:

=D13/D12

Press Enter and select the ‘%’ icon in the Number group under the Home tab.

The precise percentage value will appear in cell D14:

Method 4 – Using Excel VBA

STEPS:

Select Visual Basic under the Developer tab.

A new window will pop out.

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