How to cal. monthly salary for mid hire/termination using Sumifs or SUmproduct

[ShriHanuman]

Hello,
I am trying to estimate the monthly salary for mid month hire/terminated employees. In the attached model, the data is summarized in the "Analysis" tab and DBV tab contains the raw data.
The Analysis tab is arranged by Year, Quarter and then month(column MC onwards). The monthly section contains the "Sumproduct" equation to bring the data from DBV to Anlaysis tab. The monthly salary (Annual/12)is calculated in column L of DBV tab. The issue is that I am assuming full month salary for mid month hire or terminated employees. How do I ensure that if a person who joins on 10th of a month gets paid for only the remaining working day of the month? Similarly for terminated employees should get paid for days worked during the termination month and not the entire month. How do I change the equation?


Equation:
=SUMPRODUCT(--('HC DBV'!$G$9:$G$645=Analysis!$I46),--('HC DBV'!$C$9:$C$645<=MC$1),--('HC DBV'!$D$9:$D$645>MD$1),'HC DBV'!$L$9:$L$645)+SUMPRODUCT(--('HC DBV'!$G$9:$G$645=Analysis!$I46),--('HC DBV'!$C$9:$C$645<=MC$1),--('HC DBV'!$D$9:$D$645=""),'HC DBV'!$L$9:$L$645)

The first sumproduct estimates the salary for the person hired on or before the month and terminated in the future. The second sum product estimates the salary for active employees hired on or before the month.

[DJunqueira]

Just a suggestion to improve your worksheet with tables.

[ShriHanuman]

Thanks for the suggetion

[JeteMc]

It seems to me that there would be three groups that need to be summed.
1. Those that were hired before or on the first day of the month AND terminated on or after the last day of the month OR not terminated.
2. Those that were hired before or on the first day of the month AND terminated before the last day of the month.
3. Those that were hired after the first day of the month AND before the last day of the month AND terminated on or after the last day of the month OR not terminated.
I believe that the following formula covers those three groups:

Please Login or Register  to view this content.

Note that after the formula is activated in cell MC46 the fill handle may be dragged down to MC47 and then, while MC46:MC47 are still selected, over to cell MN47.
Let us know if you have any questions.

EDIT: I forgot to mention that I added two columns (M:N) on the HC DBV sheet (see the attached file).
Column M shows the salary for the first month of employment using: =(EOMONTH(C9,0)-C9)/DAY(EOMONTH(C9,0))*L9
Column N shows the salary for the last month of employment using: =IF(D9="",1,(D9-(EOMONTH(D9,-1)+1))/DAY(EOMONTH(D9,0)))*L9

[ShriHanuman]

It seems to me that there would be three groups that need to be summed.
1. Those that were hired before or on the first day of the month AND terminated on or after the last day of the month OR not terminated.
2. Those that were hired before or on the first day of the month AND terminated before the last day of the month.
3. Those that were hired after the first day of the month AND before the last day of the month AND terminated on or after the last day of the month OR not terminated.
I believe that the following formula covers those three groups:

Please Login or Register  to view this content.

Note that after the formula is activated in cell MC46 the fill handle may be dragged down to MC47 and then, while MC46:MC47 are still selected, over to cell MN47.
Let us know if you have any questions.

EDIT: I forgot to mention that I added two columns (M:N) on the HC DBV sheet (see the attached file).
Column M shows the salary for the first month of employment using: =(EOMONTH(C9,0)-C9)/DAY(EOMONTH(C9,0))*L9
Column N shows the salary for the last month of employment using: =IF(D9="",1,(D9-(EOMONTH(D9,-1)+1))/DAY(EOMONTH(D9,0)))*L9

Thank you for the reply. Appreciate it!.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[ShriHanuman]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Done. Thanks

[ShriHanuman]

It seems to me that there would be three groups that need to be summed.
1. Those that were hired before or on the first day of the month AND terminated on or after the last day of the month OR not terminated.
2. Those that were hired before or on the first day of the month AND terminated before the last day of the month.
3. Those that were hired after the first day of the month AND before the last day of the month AND terminated on or after the last day of the month OR not terminated.
I believe that the following formula covers those three groups:

Please Login or Register  to view this content.

Note that after the formula is activated in cell MC46 the fill handle may be dragged down to MC47 and then, while MC46:MC47 are still selected, over to cell MN47.
Let us know if you have any questions.

EDIT: I forgot to mention that I added two columns (M:N) on the HC DBV sheet (see the attached file).
Column M shows the salary for the first month of employment using: =(EOMONTH(C9,0)-C9)/DAY(EOMONTH(C9,0))*L9
Column N shows the salary for the last month of employment using: =IF(D9="",1,(D9-(EOMONTH(D9,-1)+1))/DAY(EOMONTH(D9,0)))*L9

One quick follow-up question: When you write (('HC DBV'!$D$9:$D$645>=MC$1)+('HC DBV'!$D$9:$D$645="")) and multiple each term what do these individual terms return if they are true. Normally --(A=B) return 1 if true.
I was trying to write an array function for your equation using "--" and it did not work. Example: SUMPRODUCT(--('HC DBV'!$G$9:$G$645=Analysis!$I46),--('HC DBV'!$C$9:$C$645<=EOMONTH(MC$1,-1)+1),--OR(('HC DBV'!$D$9:$D$645>=MC$1),('HC DBV'!$D$9:$D$645="")),('HC DBV'!$L$9:$L$645))

Thanks in advance.

[ShriHanuman]

Hi JeteMC,
I noticed that if I terminate all except one of the active employees in July of 2021 and terminate the remaining one in August of 2021, the compensation information is still being carried till the end of December. It does not zero out after August. Please see the attached excel.

[JeteMc]

I missed a consideration in the terminated before the end of the month segment.
Try the following:

Please Login or Register  to view this content.

In the attached file the formulas in rows 51:53 are for verification of each segment, and are not needed for the actual calculations in rows 46:47.
Let us know if you have any questions.

[ShriHanuman]

I missed a consideration in the terminated before the end of the month segment.
Try the following:

Please Login or Register  to view this content.

In the attached file the formulas in rows 51:53 are for verification of each segment, and are not needed for the actual calculations in rows 46:47.
Let us know if you have any questions.

Thank you for the reply

[JeteMc]

You're Welcome. Thank You marking the thread as 'Solved'. I hope that you have a blessed day.