# Formula required

#### ndello

##### Active Member
Hi all,
I have 4000 rows of data (amounts) in column a.
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Lisalu22

##### New Member
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

##### Active Member
Total would be great
thanks

#### jasonb75

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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

Code:
``````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

##### Active Member
I get the error #NAME?

#### jasonb75

##### Well-known Member
I seem to be having a week of name errors which version of excel are you using?

Excel 2003

Replies
5
Views
227
Replies
6
Views
204
Replies
0
Views
138
Replies
5
Views
204
Replies
3
Views
274

1,191,420
Messages
5,986,464
Members
440,031
Latest member
davidvillegasr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back