Dynamic SumIFs - Multiple Scenarios

Question

I have a headcount data structured in the following way.

Data Structure:

1. Every month I append one dataset to another by pasting values in the first empty row in one main sheet. 1a. Therefore, one employee can be included in the sheet for more than one time.
2. There is a column that tells me if the person left the company this month or if he got hired.
3. I want to have an executive summary with a comparison of two months. I managed to have this working with a static data month over month (so for instance to have a walk from July to August, October to November, etc.) with using countifs.

Question: I would like to have a dynamic selection in my summary sheet.

• if I select January in one cell and September in another, the formulas will calculate how many hires and leavers were there from January until September.
• If I select February in once cell and July in another, the formulas will calculate how many hires and leavers were there starting from February until July.

This is the exact formula I have for calculating Month Over Month change: =COUNTIFS(SSE_Reporting!$R:$R,MoM_Walk!$A5,SSE_Reporting!$AH:$AH,MoM_Walk!H$4,SSE_Reporting!$AK:$AK,MoM_Walk!U$1)

Please keep in mind below:

• My dataset contains information starting January 2019 until today (and will be increased)
  • In the executive summary, I may want just to have the view from March 2019 until December 2019 (therefore, in this case, countifs will not work, because it will count either ALL leavers or just leavers for ONE specific month)

Answer 1

You could do a COUNTIFS to count how many Leaver/Hire you got in a given date range.

Something like this could guide you to deploy your own formula:

My formula in H4 is:

=COUNTIFS($A$2:$A$28;H$3;$B$2:$B$28;">="&$F4;$B$2:$B$28;"<="&$G4)

As you can see, it works perfectly to count the criteria on multiple given date ranges.

COUNTIFS function