Formula with custom not updating in Google spreadsheet

[CatRoberts]

Hi all,

I have a custom formula in my Google spreadsheet to count red cells in column N of one of the worksheets.

This works fine, however it is not updating when any new red cells are added.

My formula is: =countCellsWithBackgroundColor("#ff0000","UK!N3:N999")

What am I missing?

Many thanks

Cat

 

[J.Ty.]

Hi,

Values of cells generally update after a change to the values in the spreadsheet. Most likely change of formatting alone does not trigger recomputation.

J.Ty.

 

[CatRoberts]

Ah! that makes sense. Although it makes my formula pretty redundant, do you know if there is there any way round this?

 

[J.Ty.]

Not really.

As a matter of fact, I am strongly against encoding meaningful information by formatting.

This information should be represented by values of cells. You can then use conditional formatting to make it more apparent.

As you have noted in your case, counting cells of certain type immediately requires a custom function (instead of a simple built-in COUNTIF), changes do not cause recomputations, etc. Pivot tables, data validations, sorting, will not work with formatting-encoded data, either. The real reason behind it is that formatting is not intended to store information and spreadsheets lack tools necessary for that purpose.

So my deepest advice is to change the construction of your spreadsheet. If you want me to help with that, please let me know.

J.Ty.

 

[CatRoberts]

That totally makes sense now. In my limited excel knowledge I thought counting the colour would be easier, it seems not. My spreadsheet has several identical pages and a final summary page. Column N in the main pages holds a formula to count how many days old a case is. If the number returns as over 21 (days), the cell turns red. A value of 21 and less and the cell turns green.I was trying to summarise how many cases are over 21 days old, which is why I thought counting the number of red cells would be an easy way to do it.Is it possible to create a formula to count the number of cells in column N that have a value of over 21?Many thanks for your time and patience.

 

[J.Ty.]

The equivalent of your formula

=countCellsWithBackgroundColor("#ff0000","UK!N3:N999")

would be

=COUNTIF(UK!N3:N999,">21")

which counts directly the number of cells with value over 21. It should respond to every change of the values you keep there.

J.Ty.

 

[CatRoberts]

Thanks so much, it works. I was totally over complicating it. I've now added an extra element and my formula reads: =COUNTIFS(UK!C3:C999,"=JANUARY",UK!N3:N999,">21")Now to do the over complicating thing again, is it possible to add another element to not count a cell if there is data in column K? This is because I realised some of the green cells have a value over 21 due to there being data entered into column K, these are the old cases that action has been taken on, so I don't need to look at them. Ignoring these means I am only counting the red cells as I originally wanted. Is this possible?

 

[J.Ty.]

In this case use

=COUNTIFS(UK!C3:C999,"=JANUARY",UK!N3:N999,">21",UK!K3:K999,"")

"" stands for 'nothing'.

J.Ty.

 

[CatRoberts]

It works! Thanks so much

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.