Forum Discussion

dickcarey460gmailcom's avatar
dickcarey460gmailcom
Copper Contributor
Feb 28, 2023

use sumif function when criteria is cell hihlighted with a color highlight

I want to use sumif function to add a column of numbers when criteria is a fill color in the cells.  How do I do this.
Excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP

dickcarey460gmailcom 

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Function CountByColor(ARange As Range, ColorCell As Range) As Long
    Dim ACell As Range
    Application.Volatile
    For Each ACell In ARange
        If ACell.Interior.Color = ColorCell.Interior.Color Then
            CountByColor = CountByColor + 1
        End If
    Next ACell
End Function

Switch back to Excel.

Use the function like this:

In the formula =CountByColor($A$1:$A$19,D1), the first argument $A$1:$A$19 is a range with colored cells, and the second argument D1 is a cell with the color that you want to count.

Save the workbook as a macro-enabled workbook (*.xlsm), and make sure that you allow macros when you open it.

Warning: a formula like this will not automatically update its result when you change the fill color of a cell. It will be updated the next time Excel recalculates formulas; you can force this by pressing F9.

 

I have attached a sample workbook with the code.

Chuks1890's avatar
Chuks1890
Copper Contributor
Jul 13, 2023

HansVogelaar  How do use sumifs with cell color as part of the criteria?

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 13, 2023

    Chuks1890 

    Do you want to impose both a color condition AND other criteria? That could become complicated.

    Can you provide an example of what you want to do?

    • Chuks1890's avatar
      Chuks1890
      Copper Contributor
      Jul 17, 2023

      HansVogelaar  See the picture below;

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 17, 2023

        Chuks1890 

        Thanks. See the attached demo workbook. The function used is

        Function SumIfColour(SumRange As Range, CriteriaRange As Range, Criteria As Variant, _
        ColourRange As Range, ColourCell As Range) As Double
    Dim i As Long
    Dim r As Double
    For i = 1 To SumRange.Count
        If CriteriaRange(i).Value = Criteria And ColourRange(i).Interior.Color = _
                ColourCell.Interior.Color Then
            r = r + Val(SumRange(i).Value)
        End If
    Next i
    SumIfColour = r
End Function

Resources