Sum by cell colour and column data

[Debhog]

Hello All,

I dont normally post but I would be so very, very chuffed if I could find a solution for this.

I want to be able to sum a column of numbers in say column B based on whether the cell is left white and the data in column A.

eg.:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Apple</TD><TD class=xl22 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: #ccffcc" align=right width=64 x:num>56</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Orange</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" align=right x:num>625</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Melon</TD><TD class=xl22 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: #ccffcc" align=right x:num>624</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Apple</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" align=right x:num>22</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Apple</TD><TD class=xl22 style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: #ccffcc" align=right x:num>45</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Orange</TD><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; BACKGROUND-COLOR: transparent" align=right x:num>668</TD></TR></TBODY></TABLE>

Is it possible to sum the amount of apples, oranges, melons only if the cell content in column "B" is white?

Debs

 

[SteveO59L]

How are the cells coloured ?

 

[Debhog]

Manually, not by conditional formatting.

 

[SteveO59L]

Populate a8:a10 with your unique values(Apple, Orange, Melon)

Then something along the lines of

Dim c1 As Range, rng1
Dim c2 As Range, rng2

Dim sum_colour As Integer


Set rng1 = Range("a8:a10")
Set rng2 = Range("b1:b6")

For Each c1 In rng1
For Each c2 In rng2
If c2.Interior.ColorIndex = 2 And c2.Offset(0, -1).Value = c1.Value Then

sum_colour = sum_colour + c2.Value
End If
Next c2
c1.Offset(0, 1) = sum_colour

sum_colour = 0
Next c1

 

[Debhog]

Thank you for your reply Steve however, I think I am out of my depth. I assume I copy this coding to a visual basic module? What formulas would I use in my worksheet?

Sorry, but I am a pretty basic user

Debs

 

[SteveO59L]

Use the Macro recorder to record a new macro.

Post the code between the sub/end sub lines produced