I've found threads that addressed OR logic or operator logic but none together and it's not working for me.
Let's say I have a table like this:
Col1 Col2(Date) Col3(Date)I want to use countifs to get the number of rows where Col2, Col3 is either Blank OR is >= Date_Variable(A date I've defined in Define Names)
I've tried the formula but clearly the syntax is wrong
=COUNTIFS(Col2,{"",">="&Date_Variable},Col3,{"",">="&Date_Variable})2 Answers
Welcome to SuperUser!
Generally speaking, when you want to use CountIF and your logic becomes more complex than 'Is this cell equal or not equal to X', it's best to make a new column that does the intermediate logic for you.
In this case, you'd make a Col4 with the following formula (if I've interpreted your brief correctly), where Magic is your predefined cell reference and the row is 1.
=OR(B1="", C1="", B1>=Magic, C1>=Magic)
Then you can simply do a COUNTIF(Col4, 1) (because TRUE = 1 in Excel). You can even hide Col4 if you don't want it shown, such as in a printed report.
With four possibilities, you may want to abandon COUNTIFS and use SUMPRODUCT. However, due to SUMPRODUCT's literal processing, you should cut the ranges down to what is actually needed and avoid full column ranges.
=SUMPRODUCT(SIGN((B2:B9="")+(C2:C9="")+(ISNUMBER(B2:B9)*(B2:B9>=Date_Variable))+(ISNUMBER(C2:C9)*(C2:C9>=Date_Variable))))
