How to count sum cell based color
- Thread starter aamirsq
- Start date
Hi aamirsq,
This type of question has already been asked in this forum but I can not remember the exact thread for the same.You may want to search with key worrds at the top right corner( google custom search).
However, there is no direct worksheet function that that is capable of achieving your requirement. But you can do this by using some writing simple UDF. Copy paste the below code in a standard module (press ALT+F11 from keyboard to bring the VB editor and insert the module).
[pre]
[/pre]
Now, go back to your worksheet.
Say, your data ranges from A1 to A5 as follows:
3
5
5
4
6
A1, A3 and A5 are filled with the color yellow.
Now, formula to get the sum of the cells which are colored:
=SumCountUDF(A1,A1:A5,TRUE) ENTER
[A1 is the color (contains data) for which you want to get the sum in the range of A1 to A5]
Formula to get the sum of the cells which are colored:
=SumCountUDF(A1,A1:A5) ENTER
The only catch is, if you make any changes in the data range, formula will not automatically recalculate the result. You need to double click on the formula cell ( or press F2), adjust the data range and then hit ENTER.
Hope this helps.
Kaushik
This type of question has already been asked in this forum but I can not remember the exact thread for the same.You may want to search with key worrds at the top right corner( google custom search).
However, there is no direct worksheet function that that is capable of achieving your requirement. But you can do this by using some writing simple UDF. Copy paste the below code in a standard module (press ALT+F11 from keyboard to bring the VB editor and insert the module).
[pre]
Code:
Function SumCountUDF(MyColor As Range, MyRange As Range, Optional SUM As Boolean)
Dim MyCell As Range
Dim MyCol As Long
Dim MyResult
MyCol = MyColor.Interior.ColorIndex
If SUM = True Then
For Each MyCell In MyRange
If MyCell.Interior.ColorIndex = MyCol Then
MyResult = WorksheetFunction.SUM(MyCell, MyResult)
End If
Next MyCell
Else
For Each MyCell In MyRange
If MyCell.Interior.ColorIndex = MyCol Then
MyResult = 1 + MyResult
End If
Next MyCell
End If
SumCountUDF = MyResult
End FunctionNow, go back to your worksheet.
Say, your data ranges from A1 to A5 as follows:
3
5
5
4
6
A1, A3 and A5 are filled with the color yellow.
Now, formula to get the sum of the cells which are colored:
=SumCountUDF(A1,A1:A5,TRUE) ENTER
[A1 is the color (contains data) for which you want to get the sum in the range of A1 to A5]
Formula to get the sum of the cells which are colored:
=SumCountUDF(A1,A1:A5) ENTER
The only catch is, if you make any changes in the data range, formula will not automatically recalculate the result. You need to double click on the formula cell ( or press F2), adjust the data range and then hit ENTER.
Hope this helps.
Kaushik
NARAYANK991
Excel Ninja
Hi Kaushik ,
I think you meant this :
http://chandoo.org/forums/topic/sum-of-row-by-background-color-for-positive-or-negative-numbers
Narayan
I think you meant this :
http://chandoo.org/forums/topic/sum-of-row-by-background-color-for-positive-or-negative-numbers
Narayan
Last edited by a moderator:
NARAYANK991
Excel Ninja
Hi Aamir ,
Instead of ColorIndex , use Color :
Replace the following statements :
[pre]
[/pre]
Narayan
Instead of ColorIndex , use Color :
Replace the following statements :
[pre]
Code:
MyCol = MyColor.Interior.ColorIndex
If MyCell.Interior.ColorIndex = MyCol Then
If MyCell.Interior.ColorIndex = MyCol Then
by
MyCol = MyColor.Interior.Color
If MyCell.Interior.Color = MyCol Then
If MyCell.Interior.Color = MyCol ThenNarayan
vasim
Member
If you want the count/sum of the same color cells (assuming you don't want it as a formula, since you are using standard colors)..then
Find/CTRL+F, options, Format(click on the dropdown),choose format from cell, select the format you want the count/sum of, find all, click inside the result box, Ctrl+A/Select All.
You can see the count/sum in the below bar (besides zoom)...
Find/CTRL+F, options, Format(click on the dropdown),choose format from cell, select the format you want the count/sum of, find all, click inside the result box, Ctrl+A/Select All.
You can see the count/sum in the below bar (besides zoom)...