Sum of multiplication of two columns

nicolegf

Hello! I would like to get the year spend for new hires in a company based on their expected start date and percent time allocation. I have a sheet with multiple departments, salary budget, percent allocation for hires (i.e., 100%, 50%, etc), and expected start date. So what I would like to do is:

For Department "X" and for people who have been Hired (i.e. my two conditions):

1. total sum of the multiplication of two columns -- salary budget * %allocation. (so, the sum of, for example, someone hired at $100,000 and for 85% of their time ($100,000*.85), plus, someone hired for $150,000 and 100% of their time ($150000*1). I am thinking the formula has to have my two "IFS"/Conditionals --> {{Range Department}, "Department", {Range Status}, "Hired") -- and then the sum of the multiplication of the two columns. That said, I dont know how to do the sum of the multiplications of two columns if those two conditional statements are met.
2. Then I would want to estimate the amount of the year that Hired person will actually be working to get the salary spend for that person in 2022 (i.e., if their salary is $100K for the year, but they start in October and are only working 3 months, then they would only cost $25K to the company this year). So it would be DATE(2022, 12, 31) - {EXPECTED START DATE} * (1/365). I would want this calculation to have the same above conditions.
3. Then I would multiple the output of steps 1 and 2

I believe I have the logic/steps down, but am struggling with the formulas. Thank you in advance for your help!! My goal is to create a dashboard that shows the annualized spend and also the actual spend for 2022 for new hires.

Rich Coles

Hi @nicolegf

I got slightly lost in your description, but thinking this should help get you to your answer... Formulas used as follows:

Salary YTD: =IF([Hire date]@row > TODAY(), "", ((TODAY() - [Hire date]@row) / 364) * [Salary % allocation]@row)

Salary due in 2022: =(((DATE(2022, 12, 31)) - [Hire date]@row) / 364) * [Salary % allocation]@row

YTD metric: =SUMIFS([Salary YTD]:[Salary YTD], Department:Department, [Dept metric]@row)

2022 metric: =SUMIFS([Salary due in 2022]:[Salary due in 2022], Department:Department, [Dept metric]@row)

I find it best to break down formulas into their constituent parts so yo know they are working then piece them back together when/if needed

Rich @ Prodactive

nicolegf

Hi @Rich Coles - thank you! The area I am having trouble with is that - because my end goal is to create an automated dashboard out of this data and new data that is submitted - I am trying to refrain from creating the the cells you have in white in your image. So I am linking all of the formulas to the source sheet (the one that automatically updates when new form submissions for new hires are submitted). If I link the cells in my "calculations sheet" (the sheet I will reference to create my dashboard) - I can perform your above formulas, but then the dashboard would not be automated with new hire submissions. Using the formulas you've shared + referencing my source sheet (the one that automatically updates with new form submissions), I am getting all "UNPARSEABLE." Let me know if this is making sense! Thank you again for your help!

Rich Coles

Hi @nicolegf - thanks for the reply. I put everything into one sheet to make it easy to visualise in this thread. All this can work off your source data sheet (I'm assuming the first 5 columns in my screenshot) that can be added via a form etc, and the Salary YTD / 2022 columns could be column formulas that kick in when new data is added. I would then have the grey columns in a metrics sheet and run the dashboards off these metrics.

Happy to have a quick call with you so I can understand what's causing your formula issue if you want to book something up via my website https://www.prodactive.co.uk/

Rich @ Prodactive