Cell References in SUM(COUNTIFS) function with multiple criteria in one column

[BlueHeron16]

I have the following formula that works perfectly until I replace dates with cell references:
=SUM(COUNTIFS(HouseMstr[RELEASE_DATE],"<=2/29/2020",HouseMstr[RATIFIED_DATE],{"",">2/29/2020"},HouseMstr[SETTLEMENT_DATE],{"";">2/29/2020"}))

I use the following format for Criteria2 and Criteria3 cell references:
">"&'Report Parameters'!$B$2

I am able to replace the date with a cell reference for Criteria1, but when replacing dates with cell references for Criteria2 and Criteria3, I receive the "There's a problem with this formula" error message:

Below is my formula with a cell reference in Criteria1, and my attempt at a cell reference in Criteria2; I noted where the flashing cursor appears after closing the error message:
=SUM(COUNTIFS(HouseMstr[RELEASE_DATE],"<="&'Report Parameters'!$B$2,HouseMstr[RATIFIED_DATE],{"",">"[flashing cursor here]&'Report Parameters'!$B$2},HouseMstr[SETTLEMENT_DATE],{"";">2/29/2020"}))

All dates in my formula need to be changed to a cell reference. How is this possible?
The formula needs to return the count for all houses with a Release Date <=X AND Ratified Date that is blank or >X AND Settlement Date that is blank or >X.

Thank you for your help!

 

[steve the fish]

Hi. This should produce same result i believe:

=SUMPRODUCT(--((HouseMstr[RELEASE_DATE]<>"")*(HouseMstr[RELEASE_DATE]<'Report Parameters'!$B$2)),--((HouseMstr[RATIFIED_DATE]="")+(HouseMstr[RATIFIED_DATE]>='Report Parameters'!$B$2)),--((HouseMstr[SETTLEMENT_DATE]="")+(HouseMstr[SETTLEMENT_DATE]>='Report Parameters'!$B$2)))

 

[BlueHeron16]

Thank you! The formula works perfectly!

 

[Domenic]

For your original formula, you can replace...

{"",">"&'Report Parameters'!$B$2}

with

CHOOSE({1,2},"",">"&'Report Parameters'!$B$2)

Hope this helps!