0

trying to use the formula below in my conditional formatting I'm getting a 'too few arguments' error -

=COUNTIFS(Ratecard!E:E,AW3, Ratecard!G:G,BA3, AN3 > 0) = 0

Any thoughts?

EchoL
  • 11
  • 5
  • the last 3 criteria, what are you trying to do with it? Please explain what you want your formula to do. – Scott Craner Jan 04 '17 at 15:22
  • Hey Scott,The first criteria checks that the value in AW3 exists in column E of Ratecard, the second value checks that the value in BA3 exists in column G of Ratecard - At this point if both values don't exist then 0 -- This works. – EchoL Jan 04 '17 at 15:43
  • The third is checking that the associated hours are greater than zero. Basically I want to highlight in Red on a Time extract where the rate is wrong and hours are greater than zero, or in orange where the rate is wrong, but the hours are zero.. – EchoL Jan 04 '17 at 15:45
  • The other thing is that I can't copy the conditional formula down... :( – EchoL Jan 04 '17 at 15:46
  • This=COUNTIFS(Ratecard!E:E,AW3, Ratecard!G:G,BA3) = 0 – EchoL Jan 04 '17 at 15:47
  • Then you would want to use and OR()... : `=OR(COUNTIFS(Ratecard!E:E,AW3, Ratecard!G:G,BA3)=0,AN3>0)` – Scott Craner Jan 04 '17 at 15:48
  • works fine, its only when I add the 'AN3 > 0' that it fails.. but even the above won't copy down – EchoL Jan 04 '17 at 15:48
  • That works a treat - gives True or False (Thank you) ,.. however, what I really want to do is -- If AN3 > 0 and Countifs = 0 'Red' IF AN3 = 0 and Countifs = 0 'Orange' Any thoughts ? Thank you – EchoL Jan 04 '17 at 16:53
  • sure, you will need two formulas each with their own rule in conditional formatting. use the same formula, just change the `>` to `=` for the second. – Scott Craner Jan 04 '17 at 16:58
  • Sorry,.. There's a third option here! Which is no formatting, -- IF AN3 > 0 and Countifs > 0 - no formatting - Everything is correct.... – EchoL Jan 04 '17 at 17:11
  • Each option gets it's own rule with its own formula. Change the formula above to your needs for each. – Scott Craner Jan 04 '17 at 17:14
  • Many thanks Scott, got that working perfectly now. – EchoL Jan 05 '17 at 11:07

0 Answers0