Add cells by different color types 1

[gsgriffin]

Here's a question that I could REALLY use help with...

I have a matrix with about 20 columns and 500 rows. The values inside the matrix has been given different background colors to provide a visual indicator as to what type of value they are. I have Red, Yellow, and Green cells.

Problem: I must be able to total each column and break out the sum value for the red cells, yellow cells, and green cells.

How can I do this?

Example:

12 5 4
6 8 7
2 3 7
4 8 4

The answers I need are:
Column 1: Red=16 Yellow=6 Green=2
Column 2: Red=8 Yellow=8 Green=8
Column 3: Red=11 Yellow=11 Green=0

 

[bluedragon2]

Not sure of a formula that would work the way you want. I do believe you can do it with code. You will have to get one of the code wizards to help you with that. But I do suggest that using color as an indicator is really not a great idea to me. Having a control with the number will allow you to do more thinges via formulas with your data. For example, if you had a column beside each data column with a R, G, or Y you would be able to do subtotals for each very easily. Just my 2 cents...

Blue

 

[jsoule]

You can also try creating your own custom formula. You will just have to do each one seperately. Hope this works.

 

[Bowers74]

Do you have a set Conditional Formatting to set the color for each of the values?

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Here are a few Functions that I created that might help. You can use them just like the SUM() function, but only for number values!

Add these to a module in your workbook:

Function SumRed(Values As Range) As Double
Dim c As Range
SumRed = 0
For Each c In Values
    If c.Font.ColorIndex = 3 Then
        SumRed = SumRed + c.Value
    End If
Next c
End Function
'--------------------------------------------------------
Function SumYellow(Values As Range) As Double
Dim c As Range
SumYellow = 0
For Each c In Values
    If c.Font.ColorIndex = 6 Then
        SumYellow = SumYellow + c.Value
    End If
Next c
End Function
'--------------------------------------------------------
Function SumGreen(Values As Range) As Double
Dim c As Range
SumGreen = 0
For Each c In Values
    If c.Font.ColorIndex = 10 Then
        SumGreen = SumGreen + c.Value
    End If
Next c
End Function

After that you just have to enter the following formulas into your worksheet:

To sum the red values:
=SumRed(YourRange)

To sum the yellow values:
=SumYellow(YourRange)

To sum the green values:
=SumGreen(YourRange)

I hope this helps!



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[gsgriffin]

Bowers74...Thank you. I posted the same question to the VBA forum and got basically same function rolled up into single output. I then split apart the function to be nearly identical to yours. You got it exactly right!!

Thanks!!!

gsgriffin