Sum or Countifs function with if statement

[ebalch]

I am trying to summarize employees in training by location based on effective date of new location and current location.
Basically need to count out the employees from being productive in their current location, or location based on effective date of new location.

Here is essentially what I am trying to achieve with a formula and cannot get the correct solution:
If the month is equal to or less than the effective date of new location, then count status of training for New location, if new location effective date is greater than training month, then count current location.

In the attached you will see for January, there are 3 employees in training, 3 from the LAX Location and 1 from the SEA location, which is prior to the effective date of their new location. Then in February , there are 4 employees in training 2 from LAX based on New Location effective date, 1 SFO and 1 SEA since February is prior to the effective date of their new location.


Thanks, look forward to solutions. I have tried ifs with countifs and not getting desired results.
Cheers,
Ellen

 

[ebalch]

I am trying to summarize employees in training by location based on effective date of new location and current location.
Basically need to count out the employees from being productive in their current location, or location based on effective date of new location.

Here is essentially what I am trying to achieve with a formula and cannot get the correct solution:
If the month is equal to or less than the effective date of new location, then count status of training for New location, if new location effective date is greater than training month, then count current location.

In the attached you will see for January, there are 3 employees in training, 3 from the LAX Location and 1 from the SEA location, which is prior to the effective date of their new location. Then in February , there are 4 employees in training 2 from LAX based on New Location effective date, 1 SFO and 1 SEA since February is prior to the effective date of their new location.


Thanks, look forward to solutions. I have tried ifs with countifs and not getting desired results.
Cheers,
Ellen

 

[Peter Bartholomew]

A number of possible solutions, primarily aimed at 365

= LET(
   currentTraining, COUNTIFS(
      currentLoc, location,
      trainingStart, "<="& EOMONTH(+reportingDate,0),
      trainingEnd, ">="& reportingDate,
      transferDate, ">="& reportingDate),
   futureTraining,  COUNTIFS(
      newLoc, location,
      trainingStart, "<="& EOMONTH(+reportingDate,0),
      trainingEnd, ">="& reportingDate,
      transferDate, "<"& reportingDate),
   currentTraining + futureTraining )