Countifs using open ended date range

%3CLINGO-SUB%20id%3D%22lingo-sub-1682971%22%20slang%3D%22en-US%22%3ECountifs%20using%20open%20ended%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682971%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20following%20formula%20to%20count%20the%20number%20of%20involvements%20an%20individual%20has%20had%20whist%20accessing%20the%20service.%26nbsp%3B%20However%2C%20some%20cases%20are%20still%20open%20and%20for%20those%20that%20have%20no%20closing%20date%20it%20will%20not%20return%20a%20value%20-%20any%20suggestions%20please...%3C%2FP%3E%3CP%3E%3CFONT%3ECOUNTIFS('F%26amp%3BG'!D%3AD%2CB4%2C'F%26amp%3BG'!L%3AL%2C%22%26gt%3B%3D%22%26amp%3BF4%2C'F%26amp%3BG'!L%3AL%2C%22%26lt%3B%3D%22%26amp%3BG4)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1682971%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1683126%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20using%20open%20ended%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683126%22%20slang%3D%22en-US%22%3EFor%20better%20understanding%20please%20share%20some%20sample%20data%2C%20help%20us%20to%20fix%20the%20issue.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1683299%22%20slang%3D%22en-US%22%3ERE%3A%20Countifs%20using%20open%20ended%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683299%22%20slang%3D%22en-US%22%3EHaving%20removed%20data%20the%20problem%20can%20be%20seen%20in%20Column%20I.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am using the following formula to count the number of involvements an individual has had whist accessing the service.  However, some cases are still open and for those that have no closing date it will not return a value - any suggestions please...

COUNTIFS('F&G'!D:D,B4,'F&G'!L:L,">="&F4,'F&G'!L:L,"<="&G4)

6 Replies
For better understanding please share some sample data, help us to fix the issue.
Highlighted
Having removed data the problem can be seen in Column I.
Highlighted

@DaisyDuke1 

Instead of G4 in formula you may use IF(G4="",TODAY(),G4)

Highlighted

@Sergei Baklan 

Thanks, I have tried this but it doesn't work - unless I am misunderstanding what you are telling me to do??

Highlighted

@DaisyDuke1 

Hi @Sergei Baklan - I have worked out what you were telling me and yes it works!!  Thank you so much I am extremely grateful for your support.

Highlighted

@DaisyDuke1 , you are welcome, glad to help