Sum and average cells based on background color

**Kaitlynn** · 08-22-2013, 10:35 AM

I have a spreadsheet of data that has been categorized by cell background color. I need to find the total sum and total averages for various data types on the spreadsheet. Right now, I have a macro set up that sums data based on the cells background color, but I can't figure out how to average cells based on color so I've been manually doing that. Is there a way I can use a macro to take the sum of certain data types and average other data types on the same tab? Below is the current macro I'm using:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
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

I'm new to macros, so any and all help/advice is appreciated!! Thank you!

**BigBas** · 08-22-2013, 10:58 AM

Hi Kaitlynn,

Looking at your code, it seems that it has two options built into it. Option TRUE sums the range, and option FALSE counts the range. Using that logic, the average can be determined by dividing the sum by the count.

For example,

=ColorFunction(A1,B1:B10,TRUE)/ColorFunction(A1,B1:B10,FALSE)

Obviously, without your data, I can't test to see if it works correctly. Try it out and let us know if that's whats needed

**millz** · 08-22-2013, 10:59 AM

Please Login or Register  to view this content.

**Kaitlynn** · 08-22-2013, 12:34 PM

Thanks for the responses! millz, I tried your code but it seems to be averaging all the data in the selected range, instead of only the cells with a specific color? I attached a sample of the original data; the column titled "Cost Per 1,000 People Reached" is the data that needs to be averaged. Thanks for the help!