Google Sheets Conditional Formatting of a column based on formula - Stack Overflow

admin管理员组

文章数量:1397142

I cannot figure out the correct syntax to get the conditional formatting I need in google sheets. In sheet1 I have column B with header name Count and then a sequence of 1 to 100 in the rows. In sheet2 I some other counts to create a total. The condition I need to check against is COUNTIF('sheet2'!$B:$B,"mytotal"). (I've named this cell CurrentTotal). I want to turn the cell in sheet1 red based on what my total is.

I have tried various custom formula conditional rules but I haven't managed to find anything that works either by referencing the COUNTIF formula or the named cell (with or without using INDIRECT). What should be the correct setup I should be using to get this to work?

Sheet1.

| Count | | 1 | | 2 | | 3 | | 4 | | 5 |

Sheet2. This is a column of info. The important bit is the count of instances of mytotal.

| Total | | mytotal | | mytotal | | x | | y | | mytotal |

So the countif will be 3 so sheet1 should have the 3 highlighted. If sheet2 gets updated to have an extra mytotal then the highlight on sheet1 should move to 4.

I cannot figure out the correct syntax to get the conditional formatting I need in google sheets. In sheet1 I have column B with header name Count and then a sequence of 1 to 100 in the rows. In sheet2 I some other counts to create a total. The condition I need to check against is COUNTIF('sheet2'!$B:$B,"mytotal"). (I've named this cell CurrentTotal). I want to turn the cell in sheet1 red based on what my total is.

I have tried various custom formula conditional rules but I haven't managed to find anything that works either by referencing the COUNTIF formula or the named cell (with or without using INDIRECT). What should be the correct setup I should be using to get this to work?

Sheet1.

| Count | | 1 | | 2 | | 3 | | 4 | | 5 |

Sheet2. This is a column of info. The important bit is the count of instances of mytotal.

| Total | | mytotal | | mytotal | | x | | y | | mytotal |

So the countif will be 3 so sheet1 should have the 3 highlighted. If sheet2 gets updated to have an extra mytotal then the highlight on sheet1 should move to 4.

• google-sheets
• conditional-formatting

Share Improve this question Follow edited Mar 27 at 9:18 Graham Pursey asked Mar 26 at 20:28 Graham PurseyGraham Pursey 322 bronze badges 3

• Welcome to StackOverflow! Please provide a minimal reproducible example in the form of markdown tables containing your sample input data and desired output. You may create one with the help of this link. – PatrickdC Commented Mar 26 at 21:50
• please place it on your posted question and not in the comments section. – PatrickdC Commented Mar 26 at 22:04
• Your sheet 2 is a bit unclear. Please consider editing the question again so that others, who may be confused, can understand it. – Gyul Commented Mar 26 at 22:22

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You're very close. Remember that custom formulas for the conditional formatting criteria are evaluated for each cell that the rule is being applied to and the formula needs to return TRUE/FALSE.

Also, in order to reference a range on another sheet, you need to use INDIRECT.

In your case, you are applying the rule to Sheet1!B2:B, so writing the custom formula from B2's perspective gives:

=B2=COUNTIF(INDIRECT("Sheet2!B:B"),"mytotal")

本文标签： Google Sheets Conditional Formatting of a column based on formulaStack Overflow