Dynamic SumIFs - Multiple Scenarios

1
votes

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)

enter image description here

enter image description here

1
excelformulacountifsumifs
Use sumifs() and specify starting month and leaving month as the controlling criteria. - Solar Mike
That's how I did this for analytics of Month and Month+1 (for instance July to August, November to December). The problem is when the interval is bigger than one month. Let's take July to October as an example. If I use sumifs and leaving month will be October, it will show me only employees who left in October. What about the ones who left in August, September? Of course I can use sumifs+sumifs+sumifs+ etc but it will not be dynamic... - Marek Re
the formulas will calculate how many hires and leavers were This sounds like a simple COUNTIF. What have you tried until now? What formulas? - Foxfire And Burns And Burns
This is the exact formula: =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). I know it may be a bit confusing... - Marek Re
About your question, as I said, it looks like a simple COUNTIF. You can add criterias like < or > to count a condition on a given date range (like January 2019 to December 2019). Something like =COUNTIFS (criteria_range, ">="&start_date,criteria_range,"<="&end_date) - Foxfire And Burns And Burns

1 Answers

0
votes

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:

enter image description here

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