Formula with ranges: Need help with Countifs formula

[christine007]

Hello,
I have a spreadsheet that keeps stats for case workers caseload and I am running into issues counting cases that were opened prior to 2015 and were still open as of 1/1/15. I have names ranges for all my cell references, which I have many of. I have ranges for region, agency, assigned worker, case opened, case closed, closure reason to name a few. I have formulas that are working for all other areas in my spreadsheet, but this one just not coming to me. Please help me...

I need all cases in the SM region that are a GR agency, that were opened prior to 1/1/15(C1) and closed after 1/1/15.

Here is the formulas I've tried, but are not working because they want to count closure dates after 1/1/15:
=COUNTIFS(Region,"SM",Agency,"GR",Case_Opened,"<"&C$1,Closure_Date,"")
=COUNTIFS(SW,"SM1",Agency,"GR",Case_Opened,"<"&C$1,Closure_Date,"<"&C$1)
=COUNTIFS(SW,"SM1",Agency,"CW",Case_Opened,"<"&C$1,Closure_Date,"<=1/1/2015")

I hope that is enough information to allow users to help me. I am desperate because my manager needs the data yesterday.

I appreciate any help.

Thank you!

 

[willhouston]

Hello,
I have a spreadsheet that keeps stats for case workers caseload and I am running into issues counting cases that were opened prior to 2015 and were still open as of 1/1/15. I have names ranges for all my cell references, which I have many of. I have ranges for region, agency, assigned worker, case opened, case closed, closure reason to name a few. I have formulas that are working for all other areas in my spreadsheet, but this one just not coming to me. Please help me...

I need all cases in the SM region that are a GR agency, that were opened prior to 1/1/15(C1) and closed after 1/1/15.

Here is the formulas I've tried, but are not working because they want to count closure dates after 1/1/15:
=COUNTIFS(Region,"SM",Agency,"GR",Case_Opened,"<"&C$1,Closure_Date,"")
=COUNTIFS(SW,"SM1",Agency,"GR",Case_Opened,"<"&C$1,Closure_Date,"<"&C$1)
=COUNTIFS(SW,"SM1",Agency,"CW",Case_Opened,"<"&C$1,Closure_Date,"<=1/1/2015")

I hope that is enough information to allow users to help me. I am desperate because my manager needs the data yesterday.

I appreciate any help.

Thank you!

On the last formula you posted, the "<=" at the end should be ">=". Whether the rest of it would have worked, I'm not sure. Try something like

=COUNTIFS(Region,"SM", Agency,"GR", Case_Opened, "<2015-01-01", Closure_Date, ">=2015-01-01")

Make sure all your named regions are of the same dimensions (some number of rows by one column, I assume). You can probably use any date format you want (try 01/01/15 if you want). If you need to also find cases that are not closed, replace ">=2015-01-01" with "".

Hope that helps,

Will