question about conditional sum

[jinvictor]

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?

 

[Nick Hodge]

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 Insert>Module 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
(e-mail address removed)