Sumif formula based on background color of adjacent cell

Question

I was wondering if there is a Sumif formula that will sum all the values in Column B based on whether or not the cell next to it in Column A is highlighted.

An example is the cells in A3 and A8 are highlighted yellow so I want the sum of the cells in B3 and B8 to go in F4. Is there a formula, or macro that I have to create to accomplish this?

No formula directly and you will need to write the vba. Are these cells conditionally formatted to become highlighted? Is so, what is the rule? — Scott Craner
Echoing @ScottCraner here. Here's a fairly comprehensive guide to summing/counting by color (ablebits.com/office-addins-blog/2013/12/12/…). Also as he pointed out, if the highlighting is coming from a conditional highlighting rule, you can just highlight the conditional highlighting rule and SUMIF based on that (it's more staightforward than tackling the highlighting approach in that case). — Joe
The color is coming from a Macro from a search, which is from this website: pcadvisor.co.uk/forum/helproom-1/… — Innism
Then in that code keep a running total of the cells as they are highlighted using offset and put the answer in the desired cell when complete. — Scott Craner
I'm not familiar with offset, especially in conjunction with coding. Could you provide me with an example or a resource? — Innism

VBA Pete VBA Pete · Accepted Answer · 2017-02-08T20:05:06

You could use a VBA function to sum all cells that are colored:

Code:

Public Function ColorSum(myRange As Range) As Variant

Dim rngCell As Range
Dim total As Variant

For Each rngCell In myRange.Cells

    If rngCell.Interior.ColorIndex <> -4142 Then
    total = total + rngCell.Offset(0, 1).Value
    End If

Next rngCell

ColorSum = total

End Function

Sumif formula based on background color of adjacent cell

1 Answers