Excel - Count cells or Sum cell's values by background Color (1 Viewer)

Rx_

Nothing In Moderation
Joined
Oct 22, 2009
Messages
2,803
I found this and will put it here so I can find it when I need it. If you think this is useful to share, please let me know. Otherwise, I can just put them away in my private reserve on my networked hard drive located in my private reserve wine cellar.
VBA code to count and/or sum the cells by background color:
Search words: VBA Count and or sum cells based on background color

Tools needed for this code:
Copy of MS Excel 97 or later.
A bottle of Merlot preferably northern Napa Valley.

1. Use ALT + F11 keys to open Visual Basic for Applications window while pouring wine.
2. Click Insert > Module then paste the following code in the Module Window.
Code:
Public Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then   ' Note this can be set to true or false
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
3. Save the code and apply the following formula in a cell:
To Count the colored cells use:
=colorfunction(A,B:C,FALSE)
To Sum the colored cells use:
=colorfunction(A,B:C,TRUE)

A: is the cell with the particular background color you want to calculate the count and sum.

B:C: is the cell range where you want to calculate the count and sum.

4. Take the following screenshot for example, enter the formula =colorfunction(A1,A1:D7,FALSE) to count the yellow cells. And use the formula =colorfunction(A1,A1:D7,TRUE) to sum the yellow cells.
Note: A1 will pass the background color of the cell A1 into the first argument

The second argument, A1:D7 is the range of cells to be evaluated.
The two arguments will need to be changed as needed.

See attached images of demo.

The demo shows 5 Cells A11 through A15 with the colors used.
The formula pasted in the cell uses A1 as the default. Change it to A11.
Now the background color of A11 us used to count and sum the cells that have a background of yellow.

Remember, bottle of Napa Valley Merlot is considered a valid business deduction since it is a required tool in the instructions.
 

Attachments

Last edited:

Brianwarnock

Retired
Joined
Jun 2, 2003
Messages
12,701
RX_
You need to repost after selecting Disable smilies in text option , it appears below the input form.

Brian
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom