Forum Discussion

JoshSchaf's avatar
JoshSchaf
Copper Contributor
Feb 07, 2023

AutoSum based on FILL color?

Friends-

 

     Is there a way to run an autosum on a row with various colored cells and to only sum up a particular color?  For example, I have numbers in 10 cells:  A1-A10.  I have made cells A3 & A6  the same fill color and want to know their combined sum.  Can I create either an if/then or an autosum (or a combination of the two) for this?  I realize on an example this small it would be easier to add individual cells, but if I had data in 1000 cells with 97 of them a particular fill color...

 

   Thank you!

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 07, 2023

    JoshSchaf 

    Public Function SumBackGroundColor(Selection As Range) As Long

    Dim i As Long
    Dim cell As Range
    
    For Each cell In Selection
    If cell.Interior.ColorIndex <> -4142 Then
    i = i + cell.Value
    Else

    End If
    Next cell
    SumBackGroundColor = i
    
End Function

    You can try this function which sums the values of cells if the background color isn't white.

     

    In the attached file you can enter

    =SumBackGroundColor( 

    and select a range with the mouse for which you want to sum the values of cells with background color and then press enter.

     

Resources