[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sum if cell color

Posted on 2009-12-22
5
Medium Priority
?
895 Views
Last Modified: 2012-05-08
need to sum different cells if color is yellow or red etc.
also meets criteria in b2.b22 = b25
Book2.xlsx
0
Comment
Question by:spirodem
5 Comments
 

Author Comment

by:spirodem
ID: 26109781
answer in e25
0
 
LVL 9

Expert Comment

by:suvmitra
ID: 26111154
I need more information on this.
Can you again re-draft your conditions for the attached workbook?
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 26111722
spirodem,

The code below is in the attached file. Press the button to get the results.

Patrick
Sub specialmacro()
Dim rng As Range
Dim rng2 As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long
Dim rowe As Long
Dim colm As Long
Dim ttl As Double

With Sheets("Sheet1")
    Set rng = Range(.Cells(2, "G"), .Cells(.Rows.Count, "B").End(xlUp).Offset(0, 11))
    For Each celle In rng
        On Error Resume Next
        coll.Add CStr(celle.Interior.ColorIndex), CStr(celle.Interior.ColorIndex)
    Next celle
    For colm = 7 To 13
        For rowe = 1 To coll.Count
            .Cells(rowe + 22, colm).Interior.ColorIndex = --coll(rowe)
            Set rng2 = Range(.Cells(2, colm), .Cells(rng.Rows.Count + 1, colm))
            For Each celle In rng2
                If celle.Interior.ColorIndex = --coll(rowe) Then
                    ttl = ttl + celle
                End If
                .Cells(rowe + 22, colm) = ttl
                .Cells(rowe + 22, colm).NumberFormat = "[$$-409]#,##0.00"
            Next celle
        ttl = 0
        Next rowe
    Next colm
End With

End Sub

Open in new window

sum-by-colour-01.xls
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 26114504
Hi,

Try

Kris
Function GETCOLOR(r As Range) As Long
'To get the interior color index
Application.Volatile
GETCOLOR = r.Interior.ColorIndex
End Function
Function SUMBYCC(LR As Range, CR, SR As Range, C2S As Long) As Double
'LR - Lookup Range
'CR - Criteria
'SR - Sum Range
'C2S - Color to Sum
'sum by color and one criteria
Dim a, i As Long
a = LR
If IsArray(a) Then
    For i = 1 To UBound(a, 1)
        If LCase$(a(i, 1)) = LCase$(CR) Then
            If SR.Cells(i, 1).Interior.ColorIndex = C2S Then
                SUMBYCC = SUMBYCC + SR.Cells(i, 1)
            End If
        End If
    Next
Else
    If LCase$(a) = LCase$(CR) Then
        If SR.Cells(1, 1).Interior.ColorIndex = C2S Then SUMBYCC = SR.Cells(i, 1)
    End If
End If
End Function
Function SUMBYC(SR As Range, C2S) As Double
'SR - Sum Range
'C2S - Color to Sum
'sum by color
Dim i As Long
For i = 1 To SR.Rows.Count
    If SR.Cells(i, 1).Interior.ColorIndex = C2S Then
        SUMBYC = SUMBYC + SR.Cells(i, 1)
    End If
Next
End Function

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 26115747
spirodem - Thanks for the grade - Patrick
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question