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]
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 Function
[/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