Exclude criteria if conditions met

[EasyRed]

Hi all,
I have this formula:
= COUNTIFS(Sheet!I:I,D4,Sheet!G:G,"<"&TODAY(),Sheet!H:H,"<"&TODAY())

How can I get this to ignore the last criteria if the first two criteria are met and no date is entered for the third criteria?
Thank you in advance for any assistance.

 

[ANKUSHRS1]

try this
=IF(COUNTIFS(Sheet!I:I,D4,Sheet!G:G,"<" &TODAY())=0,COUNTIFS(Sheet!I:I,D4,Sheet!G:G,"<"&TODAY(),Sheet!H:H,"<"&TODAY()),COUNTIFS(Sheet!I:I,D4,Sheet!G:G,"<" &TODAY()))

 

[EasyRed]

Hi Ankushrs1,
Thanks for the reply, but this had a similar effect to the formula I posted.
I tried a few variations, but no luck.
It did bring up a count based on the first date, but wouldn't change no matter what second date was put in.
Cheers

 

[Khalid NGO]

Hi,

Please post a sample file with manually entered expected output.

Regards,

 

[EasyRed]

Added a sample of what I am looking for with a few notes on. Hope it makes sense.
Cheers

 

[Peter Bartholomew]

I think you are overcomplicating your solution. If no extension has been given, the blank date field will automatically satisfy the condition
= (Extended_to… < TODAY())
so the formula
= COUNTIFS( Status,"Open", Agreed_Close_Out_Date, "<"&TODAY(), Extended_to…, "<"&TODAY() )
is sufficient. It works irrespective of whether an extension is given.

[Note: the names are generated from your column headers using 'Create from Selection'. That is simply because I cannot read direct cell references comfortably and never use them in worksheet formulas]

 

[EasyRed]

Hi Peter,
Thanks for this, but still won't work (unless it's something I'm doing my end). Did you try it in the workbook?
Looks the same as my original formula just with names and without the specified sheet.
I tried it and it still gives a zero count unless a date is entered into the "extended to" column
Cheers

 

[Peter Bartholomew]

Sorry, I think you are correct. Although a blank field satisfies
= (Extended_to… < TODAY())
it does not work in the COUNTIFS. I guess one possibility is to revert to SUMPRODUCT
= SUMPRODUCT( (Status="Open") * (Agreed_Close_Out_Date < TODAY() ) * ( Extended_to… < TODAY() ) )

 

[EasyRed]

Peter, you are a genius!
Thank you so much! Works a treat.
Very much appreciate your time