# How to combine these formulas in one conditional formatting rule I am checking `if column S is empty & Column Q="Consultation"` then I need to set a rule in a cell to make conditional formatting in cell `A1`. So these are my steps:

Step 1: in `X` column: `=if(AND(\$Q\$9:\$Q="Consultation",\$S\$9:\$S=""),1,0)`

Step 2: `Y9=SUM(X9:X)`

Step 3: `A1=Y9 >0` , a rule is applied.(conditional formatting custom formula)

The question is how to combine all of these steps in one Array-formula instead of depending on two results successively .

I tried this `=Arrayformula(sum(if(AND(\$Q\$9:\$Q="Consultation",\$S\$9:\$S=""),1,0)>0)` but not working and always return a value of 0

I could remove step 3 and make step 2 : `A=SUM(X9:X)>0`

• "I am checking if column S is empty & Column Q="Consultation" then i need to set a rule in a cell to make conditional formatting in cell A1". Do you want the rule to apply JUST to cell `A1` or the whole column or each cell in column `A`? Please be specific. If you can share the test sheet with expected results. Feb 13, 2021 at 22:27
• To one cell A1 only.. it will be highlighted whenever the the formula is true.. Feb 14, 2021 at 19:26

What you need is a `COUNTIFS` formula as your conditional rule for cell `A1`
``````=COUNTIFS(\$Q9:\$Q,"=Consultation",S\$9:S,"=")>0
• `why "=" !` Because you ask for column `S` to be blank. Feb 15, 2021 at 14:07
• Because that is how the syntax for `COUNTIFS` works. Please read the reference to it. Feb 15, 2021 at 14:25