b1:b10 contains numbers in 3 different colours(red, black, blue),what
can i do if i only want the sum of one colour nos, such as sum all
numbers are red colour?


--
jinvictor
------------------------------------------------------------------------
jinvictor's Profile: http://www.excelforum.com/member.php...o&userid=34099
View this thread: http://www.excelforum.com/showthread...hreadid=549800

Jinvictor

You cannot do this via the UI but you could use a UDF (User Defined
Function) like that below

Function SumColour(rng As Range, ColourCell As Range) As Double
Dim colourNo As Integer, x As Long
Dim myCell As Range
Application.Volatile True
colourNo = ColourCell.Font.ColorIndex
For Each myCell In rng
If myCell.Font.ColorIndex = colourNo Then
x = x + myCell.Value
End If
Next myCell
SumColour =x
End Function

You would enter

=SumColour(B1:B10,B1)

This would sum all cells in B1:B10 that matched the Font colour of B1

To implement in the workbook you want to use it, press Alt+F11 and in the
VBE go to InsertModule and paste the code here, now you can use it as
normal in this workbook

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"jinvictor" wrote
in message ...

b1:b10 contains numbers in 3 different colours(red, black, blue),what
can i do if i only want the sum of one colour nos, such as sum all
numbers are red colour?


--
jinvictor
------------------------------------------------------------------------
jinvictor's Profile:
http://www.excelforum.com/member.php...o&userid=34099
View this thread: http://www.excelforum.com/showthread...hreadid=549800