Countifs with OR criteria

[CarolynS]

Hi

I'm trying to write a formula to count records that have a start date that falls between certain dates and either no end date (i.e. blank) OR an end date greater than X date. I've come up with the below formula but Excel is saying there's somehting wring with the formula:

=sum(countifs('Course Export'!D:D,{"",">"&Commands!E8},'Course Export'!H:H,">="&Commands!E10,'Course Export'!H:H,"<="&Commands!E11,))

The cell references in the Commands tab relate to dates.

Thanks in advance

 

[Jonmo1]

You can only put 'constant' values in a {constant array}, no variables.

I'd suggest a helper column, say column J for example
J2 and filled down
=OR(D2="",D2>Commands!E$8)

Then use
COUNTIFS('Course Export'!J:J,TRUE,'Course Export'!H:H,">="&Commands!E10,'Course Export'!H:H,"<="&Commands!E11)

 

[Marcelo Branco]

Or maybe this

=SUMPRODUCT(COUNTIFS('Course Export'!D:D,CHOOSE({1,2},"",">"&Commands!E8),'Course Export'!H:H,">="&Commands!E10,'Course Export'!H:H,"<="&Commands!E11))

M.

 

[CarolynS]

You can only put 'constant' values in a {constant array}, no variables.

Thank you, that explains why it wasn't working!

 

[Jonmo1]

You're welcome.

 

[CarolynS]

Thank you both. I'll give those suggestions a go