SUMIF by colour?
 

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
--
AOP

SUMIF by colour?
 

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).

In article ,
AOP wrote:

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

SUMIF by colour?
 

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?
--
AOP


"JE McGimpsey" wrote:

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).

In article ,
AOP wrote:

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

SUMIF by colour?
 

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:D)

or if you are using a cell where you put 100

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

where G2 would be the


--


Regards,


Peo Sjoblom



"AOP" wrote in message
...
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?
--
AOP


"JE McGimpsey" wrote:

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).

In article ,
AOP wrote:

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