count cells by color for multiple colors in range

Question

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.

hansvogelaar · Answer

Jessssssssss&nbsp;
You could use a similar macro/custom function, but add 1 to the count in the loop through the cells if
&nbsp;
mycell.Interior.ColorIndex &lt;&gt; xlColorIndexNone
&nbsp;
where mycell is the variable that refers to the cell in the loop.

jessssssssss · Answer

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

hansvogelaar · Answer

Jessssssssss&nbsp;
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)

Forum Discussion

count cells by color for multiple colors in range

Share

Resources