Hi,

I was wondering if there was a formula to add up cells based on the cell
color? These cells have names in them and no numbers

For example:

Column A Column B
white cell yellow cell
white cell white cell
yellow cell yellow cell

I need the cells that are yellow to equal 25 and the cells with no color to
equal 45.

This spreadsheet is to total the pay for about 100 people and the yellow
cells are not all in a column.

Is there a formula along the lines of: if a person's name is in a yellow
cell, then the dollar amount associated with the cell = 25

Sorry this is so long, I wasn't sure how else to explain it.

Thanks for the help,

Laura

Laura,

there are very nice tools out there that you may want to download that will
help your daily routines.
Your problem can be solved by a VBA code were you would use :
If cells's interior color = yellow (index here) then ajacent cell = 25

If you do not know VBA, get asap-utilities and this will do 95% you the job.

Igor



"pcwolfie" wrote:

> Hi,
>
> I was wondering if there was a formula to add up cells based on the cell
> color? These cells have names in them and no numbers
>
> For example:
>
> Column A Column B
> white cell yellow cell
> white cell white cell
> yellow cell yellow cell
>
> I need the cells that are yellow to equal 25 and the cells with no color to
> equal 45.
>
> This spreadsheet is to total the pay for about 100 people and the yellow
> cells are not all in a column.
>
> Is there a formula along the lines of: if a person's name is in a yellow
> cell, then the dollar amount associated with the cell = 25
>
> Sorry this is so long, I wasn't sure how else to explain it.
>
> Thanks for the help,
>
> Laura

Hi,

Add the following VBA code to a module in the Visual Basic Editor of your
workbook:

Function CountOfColors(myRange As Range, myCell As Range) As Long
Dim Counter As Integer
Dim cell As Range
Counter = 0
For Each cell In myRange
If cell.Interior.ColorIndex = myCell.Interior.ColorIndex Then
Counter = Counter + 1
End If
Next cell
CountOfColors = 25 * Counter
End Function

Suppose that the cells that you want to check are in the range A110, then
in a cell outside this range color one of the cells whatever color the cells
are that you want to find, in your example some shade of yellow. Suppose
that the cell you color is F1, then you can enter the following formula in a
cell:

=countofcolors(A110,F1)

The beauty of this function is that it can work for any color.

--
Cheers,
Shane Devenshire


"pcwolfie" wrote:

> Hi,
>
> I was wondering if there was a formula to add up cells based on the cell
> color? These cells have names in them and no numbers
>
> For example:
>
> Column A Column B
> white cell yellow cell
> white cell white cell
> yellow cell yellow cell
>
> I need the cells that are yellow to equal 25 and the cells with no color to
> equal 45.
>
> This spreadsheet is to total the pay for about 100 people and the yellow
> cells are not all in a column.
>
> Is there a formula along the lines of: if a person's name is in a yellow
> cell, then the dollar amount associated with the cell = 25
>
> Sorry this is so long, I wasn't sure how else to explain it.
>
> Thanks for the help,
>
> Laura