Countif with option to add filtering to source sheet.

[chesspupil]

=COUNTIFS(SERMCTRACKER!F7:F$99,">=" & B5,SERMCTRACKER!F7:F$99,"<=" & C5)

This is my formula, as solved at this thread.

https://www.mrexcel.com/forum/excel...ifs-using-dates-specified-external-cells.html

I am hoping that I can add to the formula:

How can I add a condtion that the LOCATION COLUMN must contain a specified DATA VALIDATED Variable listed in $D$1. (the list contains 14 locations as a drop-down) But I need a variable that allows me to pull for all locations as well, not just one of the 14.

Said another way:
Example: My results without location taken into account are 10. (the formula above) However when I select location X in a cell $D$1 now my results are some lesser number. The problem is when I plug this into the formula how do I get the COUNTIF to go back to not being concerned about the location.


=COUNTIFS(SERMCTRACKER!F7:F$99,">=" & B5,SERMCTRACKER!F7:F$99,"<=" & C5,AND (SERMCTRACKER!L7:L99=$D$1.....?..........)

Is there a way where once I do a data filter on the source sheet that only the remaining cells are used in populating the COUNTIF data?

Thanks