1

columns of filter duplicate

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

2
  • "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

1 Answer 1

1

To one cell A1 only.. it will be highlighted whenever the the formula is true..

What you need is a COUNTIFS formula as your conditional rule for cell A1

=COUNTIFS($Q9:$Q,"=Consultation",S$9:S,"=")>0
5
  • Thanks. It worked but why "=" ! Feb 15, 2021 at 14:05
  • why "=" ! Because you ask for column S to be blank. Feb 15, 2021 at 14:07
  • I mean why it's not as usual ,"" meaning blank Feb 15, 2021 at 14:15
  • 1
    Because that is how the syntax for COUNTIFS works. Please read the reference to it. Feb 15, 2021 at 14:25
  • It seems that "" or "=" are interchangeable expressions meaning blank Feb 15, 2021 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.