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

x
Solved

# sum if cell color

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

Author Comment

ID: 26109781
0

LVL 9

Expert Comment

ID: 26111154
Can you again re-draft your conditions for the attached workbook?
0

LVL 45

Accepted Solution

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
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
``````
sum-by-colour-01.xls
0

LVL 18

Expert Comment

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
``````
0

LVL 45

Expert Comment

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

## Featured Post

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.
###### Suggested Courses
Course of the Month17 days, 15 hours left to enroll

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