# count cells by color for multiple colors in range

Copper Contributor

# count cells by color for multiple colors in range

So I have the macro to count cells by color already and have it set up to count cells with 1 color in a range.

is there away to count multiple colors for the same range? I'm trying to calculate the total counts for 8 colors in 1 range.

I have color coded #s for attendance tracking, i have 8 that are colored for everything except PTO. PTO tracking is a blank fill. So if there is a way to calculate sums but excluding the blank fills that will work too. (other than using ctrl and clicking cells) i'm using these to track in another sheet in the same workbook.

3 Replies

# Re: count cells by color for multiple colors in range

You could use a similar macro/custom function, but add 1 to the count in the loop through the cells if

mycell.Interior.ColorIndex <> xlColorIndexNone

where mycell is the variable that refers to the cell in the loop.

# Re: count cells by color for multiple colors in range

This is the function i had found and used online for the count cells
I'm not really sure how i would edit it to add what i need lol

Function SumCellsByColor(data_range As Range, cell_color As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cell_color.Cells(1, 1).Interior.Color
For Each cellCurrent In data_range
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByColor = sumRes
End Function

# Re: count cells by color for multiple colors in range

Here you go:

``````Function SumNoFillCells(data_range As Range) As Double
Dim cellCurrent As Range
Dim sumRes As Double

Application.Volatile
sumRes = 0
For Each cellCurrent In data_range
If cellCurrent.Interior.ColorIndex = xlColorIndexNone Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumNoFillCells = sumRes
End Function``````

Example of usage:

=SumNoFillCells(E1:E6)