Using countifs with nested OR statement

[doug firr]

Why doesn't this work!

COUNTIFS(DB!$C:$C,"<>"&"",DB!$Q:$Q,OR(DB!$Q:$Q="-",DB!$Q:$Q>Summary!B7),DB!$N:$N,"<="&B7)

I want to say select data where:DB!C:C is not blank;
DB!Q:Q equals "-" OR is greater than B7;
DB!N:N is less than or equal to B7.
What is the proper way to nest this OR statement within the countifs statement?

 

[jamcall]

It has to do with the way COUNTIFS handles the return value from the OR... OR will return a boolean (True of False) for the criteria given, while COUNTIFS is looking for a criteria which it will use to determine its own boolean.

If you're looking to modify it so it does work, you could try:
COUNTIFS(DB!$C:$C,"<>"&"",DB!$Q:$Q,"=-",DB!$N:$N,"<="&B7)+COUNTIFS(DB!$C:$C,"<>"&"",DB!$Q:$Q,">"&Summary!B7,DB!$N:$N,"<="&B7)

HTH,
~ Jim

 

[doug firr]

Cheers Jim, got it working now

 

[Aladin Akyurek]

Why doesn't this work!

COUNTIFS(DB!$C:$C,"<>"&"",DB!$Q:$Q,OR(DB!$Q:$Q="-",DB!$Q:$Q>Summary!B7),DB!$N:$N,"<="&B7)

I want to say select data where:DB!C:C is not blank;
DB!Q:Q equals "-" OR is greater than B7;
DB!N:N is less than or equal to B7.
What is the proper way to nest this OR statement within the countifs statement?

oes this...

=SUMPRODUCT(
    COUNTIFS(
      DB!$C:$C,"<>",
      DB!$Q:$Q,CHOOSE({1,2},"-",">"&B7),
      DB!$N:$N,"<="&B7))

work for you?

 

[Marcelo Branco]

oes this...

=SUMPRODUCT(
    COUNTIFS(
      DB!$C:$C,"<>",
      DB!$Q:$Q,CHOOSE({1,2},"-",">"&B7),
      DB!$N:$N,"<="&B7))

work for you?

Aladin,

Very smart use of CHOOSE to build the array of options.

M.

 

[doug firr]

Yes, awesome formula. I already submitted the piece of work based on Jim's response though but definateley keeping this one in the back pocket

 

[Aladin Akyurek]

Yes, awesome formula. I already submitted the piece of work based on Jim's response though but definateley keeping this one in the back pocket

You are welcome. And thanks all for the kind feedback.