excel function: countif greater than date or blank

So far I have this:

=COUNTIFS(A:A,"<"&C1, B:B, OR(">"&C2,"="&""))

Everything works fine except the OR and the: "="&"". I'm not sure if I'm not checking for blanks properly or if the OR shouldn't be used here.

If there's a better way to do this than a single COUNTIFS(), let me know

1

2 Answers

Here's a different approach that bypasses COUNTIFS() altogether:

=SUMPRODUCT((A1:A10<C1)*((B1:B10>C2)+(B1:B10="")))

The three expressions in the innermost parentheses produce an array of logical values, but the arithmetic operations convert TRUE and FALSE to 1 and 0. The addition is equivalent to the OR() operation and the multiplication is equivalent to AND().

So the result of the arithmetic operations is an array with 1 wherever the A range is < C1 and the B range is either > C2 or blank. Then SUMPRODUCT() adds up the 1's in the array.

You can't reference the entire Column B in the last expression though, so you would have to adjust the ranges to fit your situation.

2

I ended up summing two countifs since it's a little easier for me to look back at and understand:

=SUM(COUNTIFS(A:A,"<"&C1,B:B,">"&C2),COUNTIFS(A:A,"<"&C1,B:B,"="&""))

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy