SUM column ranges skipping cells with background color

[pban92]

I have two columns - Name and value. When I want to SUM the range using SUM function, I get total sum. value.

But what I'd like to do is my total sum will be auto updated if I fill any cell with a color. In the example snippet (attached),
when I fill cell D12 with any color for example yellow in this case, the total value 6 in G13 needs to be updated to 5.

Is there any way to get this done?

Many thanks!

 

[KillerOfGiants]

What is the criteria for highlight a cell in a different color?

 

[pban92]

Just clicking the cell and filling cell with yellow randomly.

 

[awoohaw]

There isnt a standard excel function that will get you the FILL color of a cell. You can create a user defined function
in VBA and use that as part of your process.

If you have a "rule" you go by to color the cells, you should use that rule as the basis for selecting your data to SUM.
And, if you'd like the forum to try to solve your problem, please use the xl2bb add in (Link below) to post a mini workbook of your data.
Or at least post a table. WIth images, the forum needs to recreate your data, which is time consuming and could have errors.
Please help the forum help you.

Thanks in advance,

 

[awoohaw]

Just clicking the cell and filling cell with yellow randomly.

If you are doing this just for curiosity and testing. I suggest making another column with "YES" or "NO" as a drop down using data validation, and include that in your SUMIF calculation.

 

[shinigamilight]

This will sum all the cells which are default color. Just open the vba editor and paste this in there then use this as a regular function of excel.

Function COLORC(rng As Range)
            Dim rng2 As Range
            Dim c As Double
            For Each rng2 In rng
                    If rng2.Interior.ColorIndex = xlNone Then
                            c = c + rng2.Value
                    End If
            Next rng2
            COLORC = c
End Function

 

[pban92]

This will sum all the cells which are default color. Just open the vba editor and paste this in there then use this as a regular function of excel.

Function COLORC(rng As Range)
            Dim rng2 As Range
            Dim c As Double
            For Each rng2 In rng
                    If rng2.Interior.ColorIndex = xlNone Then
                            c = c + rng2.Value
                    End If
            Next rng2
            COLORC = c
End Function

View attachment 88846

Many thanks Shiningamilight
and others for your response.

However, I have two more follow up questions (attached my micro enabled spreadsheet)

1. In my example, I need to highlight the fruit type with yellow, not the quantity itself and get the total sum.
2. Is there a way to auto update or refresh as soon as cells are updated with colors?

Sorry can't upload mini-sheet as I'm using corporate laptop not allowing XL2BB.

 

[awoohaw]

@pban92 you can still copy and paste the data.

 

[pban92]

Many thanks Shiningamilight
and others for your response.

However, I have two more follow up questions (attached my micro enabled spreadsheet)

1. In my example, I need to highlight the fruit type with yellow, not the quantity itself and get the total sum.
2. Is there a way to auto update or refresh as soon as cells are updated with colors?

Sorry can't upload mini-sheet as I'm using corporate laptop not allowing XL2BB.

Here is my excel copy:

Fruit	Quantity
Apple	1
Orange	2
Grape	3	Total Sum
		6
Banana	10
Orange	20
Blue	30
		60