Calculations based on cell color

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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:
Alladin got it, as usual!
This message was edited by Corticus on 2002-10-03 16:18
 
Upvote 0
hi Aladin,

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


 
Upvote 0
On 2002-10-03 14:37, rubel wrote:
hi Aladin,

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.
 
Upvote 0
Aladin,

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
 
Upvote 0
On 2002-11-20 16:30, wolverine12 wrote:
Aladin,

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?
 
Upvote 0
Sorry, Aladin,

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?
 
Upvote 0
On 2002-11-20 16:43, wolverine12 wrote:
Sorry, Aladin,

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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top