COUNT or SUM Cells by Color in Excel | Quick Method and VBA Method
Learn the first steps to optimize any spreadsheet: https://www.excelpowerup.com/training
Take your Excel spreadsheets to the next level: https://www.excelpowerup.com/course
Get personalized professional support: https://www.excelpowerup.com/support
You might be frustrated and surprised that Excel doesn't have a built-in way to count cells by color. But don't give up, I've got you covered! I've discovered the 2 simplest ways to count or sum cells by color in Excel. The first way is fast, easy, and great for one-time or occasional use. The second way is more advanced, using Excel VBA, and great for creating a repeatable process or automated spreadsheet. And make sure to watch till the end for a bonus hot tip!
Website: https://www.excelpowerup.com
Instagram: / rebekahoster
TikTok: / rebekahoster
LinkedIn: / rebekah-oster
THE FUNCTION:
---------------------------------------------------------------------------------------------------------------------------------------------
Public Function CHOOSECOLOR(TargetArray As Variant, ReferenceCell As RANGE) As Variant
' Create temporary variable for the results
Dim ResultArray As Variant
Dim ResultCollection As Collection
Set ResultCollection = New Collection
' Assign variable Color as the interior color of the Reference Cell
Dim Color As Integer
Color = ReferenceCell.Interior.ColorIndex
' Loop through each cell in the Target Array
Dim j As Integer
For j = 1 To TargetArray.Count
' If the color of the cell matches the variable Color, add the cell to the Result Collection
If TargetArray(j).Interior.ColorIndex = Color Then ResultCollection.Add (TargetArray(j))
Next j
' Convert Result Collection to vertical Result Array
Dim i As Integer
ReDim ResultArray(0 To ResultCollection.Count - 1, 1 To 1) As Variant
For i = 1 To ResultCollection.Count
ResultArray(i - 1, 1) = ResultCollection(i)
Next
CHOOSECOLOR = ResultArray
End Function
---------------------------------------------------------------------------------------------------------------------------------------------
00:00 Introduction
00:36 Free Training!
00:45 Quick Method
01:26 Formula-Free Approach
02:22 AGGREGATE Formula
05:04 VBA Method
10:06 Add Recalculate Button
11:58 Multiple Columns
13:00 Sum of Cells by Color
14:17 Avoid Problem with Helper Column
