SUMIF by colour?

[Guest]

Where do I place this code and what else do I need to do for it to work?

Function SumIfByColor(InRange As Range, _
WhatColorIndex As Integer, SumRange As Range, _
Optional OfText As Boolean = False) As Variant
'
' This function will return the SUM of the values of cells in
' SumRange where the corresponding cell in InRange has a background
' color (or font color, if OfText is true) equal to WhatColorIndex.
'
Dim OK As Boolean
Dim Ndx As Long

Application.Volatile True

If (InRange.Rows.Count <> SumRange.Rows.Count) Or _
(InRange.Columns.Count <> SumRange.Columns.Count) Then
SumIfByColor = CVErr(xlErrRef)
Exit Function
End If

For Ndx = 1 To InRange.Cells.Count
If OfText = True Then
OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex)
Else
OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then
SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value
End If
Next Ndx

End Function

 

[JE McGimpsey]

Place the code in a regular code module. See

http://www.mvps.org/dmcritchie/excel/getstarted.htm

if you're not familiar with macros.

For it to work, you need to call the function, say

=SumIfByColor(A:A, 3, B:B, True)

to sum by the font color red (in the default palette).

 

[Guest]

At the moment I have column E with running total of column D. I have
conditional formatted column E for all cells under the total of 100 to turn
yellow. That works fine. What I need with this code is to sum all values in
col D that are opposite the yellow cells. Something like this:

D E
2 2
1 3
1 4
3 7
and so on

How do I do that?

 

[Peo Sjoblom]

You don't need any UDF for that, just use SUMIF and the same criteria that
you used for the formatting

=SUMIF(E:E,"<100",D)

or if you are using a cell where you put 100

=SUMIF(E:E,"<"&G2,D)

where G2 would be the


--


Regards,


Peo Sjoblom