COUNT or SUM Cells by Color in Excel | Quick Method and VBA Method скачать в хорошем качестве

COUNT or SUM Cells by Color in Excel | Quick Method and VBA Method

⚡Access FREE Cheat Sheet: https://www.excelpowerup.com/cheatsheet 💡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 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