# Calculations based on cell color

#### imported_unknown

##### Active Member
How do I perform mathematical computation based on the cell color??? The problem lies in the fact that I can color a cell based on a certain value or computation but how do I perform a computation based on color or format of a cell. For example if the cell color is green in a certain range then the cell values should be added. If any of you could be help to me it would be great.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Hi!

I got this from the Ozgrid site:
ARG2NAME.XLS
BCDE
32CellColor
333
344
3520
3619
3728
3832
Sheet1

The colors are messed up, but the formula gives you a numeric value depending on the cell's color...I think this will allow you to perform calculation depending on the color as you wanted.

This shows you how to use the formula:
ARG2NAME.XLS
ABCD
16NameRefersTo
17,
18Myref=MID(GET.CELL(6,INDIRECT("rc",FALSE)),FIND("ROW(",GET.CELL(6,INDIRECT("rc",FALSE)))+4,FIND(")",GET.CELL(6,INDIRECT("rc",FALSE)))-FIND("ROW(",GET.CELL(6,INDIRECT("rc",FALSE)))-4)
19IsFormula=GET.CELL(48,INDIRECT(myref)))+0*now()
20CellColor=get.cell(63,indirect(MyRef))+0*now()
21RowIsHidden=IF(GET.CELL(17,INDIRECT(Myref))=0,TRUE,FALSE)+0*now()
22RowHeight=GET.CELL(17,INDIRECT(Myref))+0*NOW()
23CellDispl=GET.CELL(53,INDIRECT(myref))+0*now()
24IsBold=GET.CELL(20,INDIRECT(myref))+0*NOW()
Sheet1

You could use SumIf(), for example, to add numbers in cells of a certain color.

HTH,
Corticus

edit:
This message was edited by Corticus on 2002-10-03 16:18

Thanks for your help but I still have a small question. Is there any formula that could be used to sum up all values of lets say green cells in a certain range. I don't think cell format has been of great help to me since all my cells seem to be in the same format. I was looking in summing up the values of red colored cells, green colored cells, yellow colored cells and so on.

Thanks

On 2002-10-03 14:37, rubel wrote:

Thanks for your help but I still have a small question. Is there any formula that could be used to sum up all values of lets say green cells in a certain range. I don't think cell format has been of great help to me since all my cells seem to be in the same format. I was looking in summing up the values of red colored cells, green colored cells, yellow colored cells and so on.

Thanks

=SUMPRODUCT((ExtCell("backgroundcolor",A2:A10,1)=E2)*(A2:A10))

where E2 the color index of interest.

This formula will sum all values in E2-colored cells in A2:A10.

Is there a way to get this to work with formuals? (Other than pasting the values into another column) I can get it to sum values, by colour, but I can't get it to sum values, by colour, created by a formula.

Thanks

On 2002-11-20 16:30, wolverine12 wrote:

Is there a way to get this to work with formuals? (Other than pasting the values into another column) I can get it to sum values, by colour, but I can't get it to sum values, by colour, created by a formula.

Thanks

Good to see you back. Hmmm, we have to settle that match problem which is undersnowed by tons of other things.

Do you mean by "colour created by a formula" that you have cells conditionally formatted to have a background color?

I did get the 'match' problem to work (sort of), enough that I could complete about 90% with the formula. Thanks.

As for the colour issue, I don't have a formula that returns colour, just background colour for a cell. When I use the formula below, I get #N/A. If I just cut and paste the values back in, I get the desired result. Is that more clear?

On 2002-11-20 16:43, wolverine12 wrote:

I did get the 'match' problem to work (sort of), enough that I could complete about 90% with the formula. Thanks.

As for the colour issue, I don't have a formula that returns colour, just background colour for a cell. When I use the formula below, I get #N/A. If I just cut and paste the values back in, I get the desired result. Is that more clear?

No, I'm afraid not. Which formula are you using?

Thanks for hanging in there...

Ok, I'll give all the details here:

=INDEX('Sheet Name'!P:P,MATCH(\$A311,'Sheet Name'!\$B:\$B,0),0)
The returned result is a dollar value and the cell(s)has a coloured background.

Later I use:
=SUMPRODUCT((ExtCell("backgroundcolor",Range,1)=Color Value)*(Range)
I get #n/a returned unless I replace the formula (first one above) with the actual values.

I hope that makes it more clear.

Replies
1
Views
162
Replies
2
Views
422
Replies
3
Views
321
Replies
0
Views
137
Replies
5
Views
229

1,202,977
Messages
6,052,888
Members
444,608
Latest member
Krunal_Shah

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