Using countifs with nested OR statement

doug firr

Board Regular
Joined
Mar 28, 2011
Messages
140
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?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jamcall

Active Member
Joined
Sep 19, 2013
Messages
287
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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...
Rich (BB code):
=SUMPRODUCT(
    COUNTIFS(
      DB!$C:$C,"<>",
      DB!$Q:$Q,CHOOSE({1,2},"-",">"&B7),
      DB!$N:$N,"<="&B7))
work for you?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,400

ADVERTISEMENT

oes this...
Rich (BB code):
=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

Board Regular
Joined
Mar 28, 2011
Messages
140
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,850
Messages
5,574,647
Members
412,607
Latest member
caner
Top