Скачать или смотреть How To Sum COLOURED Cell In Excel | How To Sum and Count Cell By Color In Excel

How to SUM and COUNT cells based on color in Excel.

In this video I'll teach 3 ways to SUM and COUNT cells in Excel based on color. By default, Excel doesn't have a built-in way to do sum by cell color. The first method is to use the SUBTOTAL function and the filter by color feature. The SUBTOTAL allows us to find the SUM, COUNT, AVERAGE, and more. The only problem with this is that we can't see all the data, and we can only see one total at once. The second method is using the name manager, where we can use the GET.CELL feature to find the background cell color. With this information, we can use the SUMIF and COUNTIF to find the COUNT and SUM by cell color. The main problem with this is that it's quite tedious to set up and doesn't update automatically. So the third and superior method is to use VBA to COUNT and SUM by cell color. We'll set up 2 custom functions (one for count, one for sum) so we can easily calculate this. To refresh the data, we'll create a macro and assign it to a button.



Code for BVA:-

Function sumcolor(rng As Range, colorcell As Range) As Double
Dim cell As Range, total As Double
total = 0
For Each cell In rng
If cell.Interior.Color = colorcell.Interior.Color Then
If IsNumeric(cell.Value) Then
total = total + cell.Value
End If
End If
Next cell
sumcolor = total

End Function


▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapter
Method 1: Easy & MaMaManu
Method 2: Advanced & Fully Automated