# Using countifs with nested OR statement

#### doug firr

##### Board Regular
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?

### Excel Facts

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
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

##### Board Regular
Cheers Jim, got it working now

##### MrExcel MVP
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

oes this...
Rich (BB code):
``````=SUMPRODUCT(
COUNTIFS(
DB!\$C:\$C,"<>",
DB!\$Q:\$Q,CHOOSE({1,2},"-",">"&B7),
DB!\$N:\$N,"<="&B7))``````
work for you?

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

M.

#### doug firr

##### Board Regular
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

##### MrExcel MVP
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.

Replies
6
Views
202
Replies
1
Views
146
Replies
0
Views
37
Replies
3
Views
266
Replies
16
Views
317