Sums or counts cellsbased on specified fill color (2003)

Topic

New Reply

I came across this user defined function but don’t know how to use it. I placed it in a module in my Personal.xls sheet. I think it counts the cells that have a color but don’t know how to change the code to designate a specific color to count etc…………..Thanks for your help.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

”””””””””””””””””””
‘Sums or counts cells based on a specified fill color.
”””””””””””””””””””’

lCol = rColor.Interior.ColorIndex

If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If

ColorFunction = vResult
End Function

Reply | Quote

Replies

You set the color of a cell to the color you want to count or sum and pass that cell as the first argument to the function.

Reply | Quote

OK call me stupid – When you say “You set the color of a cell to the color you want to count or sum and pass that cell as the first argument to the function” – how do you do this? With conditional formatting? How do you use the function? Do you put it at the bottom of a column – =Personal.xls!ColorFunction(A1:A10) – what is the syntax? Can you give an example of how to use this function?

Reply | Quote

Did Hans’ more complete reply answer your question?

Reply | Quote

Yes, thank you both.

Reply | Quote

To expand on Legare’s reply: the formula

=Personal.xls!ColorFunction(F1,A1:D30)

will count the number of cells in A1:D30 with the same fill color as F1. So if you make cell F1 red, the formula will count red cells, and if you make F1 blue, the formula will count blue cells.

If you want to sum the cell values instead of count the cells, use

=Personal.xls!ColorFunction(F1,A1:D30,TRUE)

Reply | Quote

How, if possible, would I change this function to sum cells where the content have a text colour? Or more to the point, not a text colour? We have a spreadsheet where amount unpaid are black and when paid they are changed to red text with strikethrough. I want to sum the black, i.e. unpaid, amounts.

David

Reply | Quote

How about this:

Function ColorFunction(rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim vResult

''''''''''''''''''''''''''''''''''''''
'Sums or counts cells based on the font not being red and struckout.
'''''''''''''''''''''''''''''''''''''''



If SUM = True Then
    For Each rCell In rRange
        With rCell.Font
            If Not (.Color = vbRed And .Strikethrough = True) Then
            vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        End With
    Next rCell
Else
    For Each rCell In rRange
        With rCell.Font
            If Not (.Color = vbRed And .Strikethrough = True) Then
                vResult = 1 + vResult
            End If
        End With
    Next rCell
End If

ColorFunction = vResult
End Function

It will sum anything that is not Red and struck out.

Reply | Quote

Thanks for the response.

=Colorfunction(S39:Z39,-1) typed into a cell gives a #NAME? error. The error message in Excel2003 says “Invalid name error” and the function does not appear in the macros list. I’ve put this function in the worksheet’s VBA module so it should show. Any ideas what I’m doing wrong?

David

Reply | Quote

The function should go in a normal module in your workbook not in the worksheet’s module. Select the workbook in the project explorer in the VBEditor, choose Insert-Module, then paste the code into the new module (removing it from the worksheet module). Note: Functions do not appear in the macro dropdown.
HTH

Reply | Quote

Rory

This now works fine if I add or delete figures within the range. However, changing an amount from black to red/strikethrough does not force a recalculation, and neither does F9 (I’m set to recalc automatically). The only way seems to be to select the cell with the formula and go into it as if to edit it; exiting forces a recalc. This is impractical.

Any thoughts?

David

Reply | Quote

You could use the worksheet selectionchange event but that’s a bit overkill I think. Personally, I would add a column to your spreadsheet labelled Paid and simply put a Y (or Yes) in it when paid. You can then have conditional formatting set up on your numbers column so that if the corresponding cell in the Paid column contains a Y, it automatically sets the Font to Red and struck through. You can then also simply use a SUMIF formula to total paid or unpaid amounts.
HTH

Reply | Quote

If you insert the line

Application.Volatile

at the beginning of the function, F9 will recalculate its result.

Reply | Quote

Brilliant, thanks Rory and Hans

David

Reply | Quote

Han s,
A new twist: Assume in column A of a worksheet, I have a list names, say 150, some of which I have formatted with different fill colors. In column B I have test scores for each respective name, but no fill color for any of these cells (I need to keep them “un-filled” for various reasons). At the bottom of column B, I would like to sum the scores for each name with a particular filled color in column A. For example, if there are 18 names in column A whose cell color is red, I would like to sum those respective scores at, say, cell B152. Any ideas?
As always, thanks!

Reply | Quote

You can use the SumIfByColor function from Functions For Cell Colors on Chip Pearson’s site for this.

Reply | Quote

Thanks Hans, I remembered that function. What I was wondering if you could accomplish the same result without having to remember each and every color index of the excel pallette?

Reply | Quote

What would you like as alternative? A cell that has the color to be used?

Reply | Quote

I don’t know. I just don’t trust myself to remember each of the color indices…anyway, might you know of something? Remember, the cells in column B must remain “unfilled.”
Thanks again.

Reply | Quote

You could use this variant of Chip Pearson’t function:

Function SumIfByColor(InRange As Range, _
ColorOf As Range, 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 that of ColorOf.

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 = ColorOf.Font.ColorIndex)
Else
OK = (InRange.Cells(Ndx).Interior.ColorIndex = ColorOf.Interior.ColorIndex)
End If
If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then
SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value
End If
Next Ndx
End Function

Say that you want to sum the values in B2:B37 for which the corresponding cell in column A has the same fill color as cell A2. You can use the formula

=SumIfByColor(A2:A37,A2,B2:B37)

If you want to look at the font color instead of the fill color, use

=SumIfByColor(A2:A37,A2,B2:B37,TRUE)

This way, you don’t have to know the color index, you simply refer to a cell that has the desired fill or text color, depending on what you want to use.

Note: the function will return #VALUE if you replace the second argument A2 with a range in the cells don’t all have the same color.

Reply | Quote

Thanks, Hans.

Reply | Quote

Thanks guys. I changed direction because I couldn’t get this figured out. Have a great day.

Reply | Quote