Formula required

[ndello]

Hi all,
I have 4000 rows of data (amounts) in column a.
Some cells are shaded yellow.
What I need is in column b - show the amount in cell a, if the cell is yellow. or not white.

Hope this explanation is enough.
Thanks

 

[Lisalu22]

so you need once cell to total up all yellow cells from row 1?
or you just need row 2 to copy the value from row 1 if and only if the cell is yellow.

 

[ndello]

Total would be great
thanks

 

[jasonb75]

are yellow cells changed by conditional format? If they are you could use sumifs with the same criteria, or sumproduct if you don't have excel 07

 

[ndello]

no, someone went through manually and highlighted the cells.
Actually the spreadsheet has 2 columns, column a has wording (could say anything) cell B has the amount.
The Yellow color is only in cell a.

In effect what I am looking for is the total amount and then the average amount.
thanks

 

[jasonb75]

ndello, I haven't tested this, it came from another forum called exceltips, just made a small change so hopefully it will do it for you.

First copy the code into a VBA module

Function SumByColor(InputRange As Range, ColorRange As Range) As Double
Dim cl As Range, TempSum As Double, ColorIndex As Integer
    
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempSum = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then
            TempSum = TempSum + cl.Offset(0, 1).Value
        End If
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    SumByColor = TempSum
End Function
Function CountByColor(InputRange As Range, ColorRange As Range) As Double
Dim cl As Range, TempCount As Integer, ColorIndex As Integer
    
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempCount = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then
            TempCount = TempCount + 1
        End If
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    CountByColor = TempCount
End Function

Next enter the formula
=SumByColor($A$1:$A$20,A1)
into the cell where you want the total, $A$1:$A$20 changed your range with the yellow cells, A1 changed to any cell that is yellow.

Then for your average use the formula

=sum(SumByColor($A$1:$A$20,A1)/CountByColor($A$1:$A$20,A1))

Hope this helps

 

[ndello]

I get the error #NAME?

 

[texasalynn]

also take a look at Chip Pearson's functions on colors
http://www.cpearson.com/excel/colors.aspx

 

[jasonb75]

I seem to be having a week of name errors which version of excel are you using?

 

[ndello]

Excel 2003