Job Role Costs Calculation

Question

I am working with a table that includes a column for job roles, selected from a dropdown list, and a column for hours. When hours are entered, the spreadsheet should calculate the corresponding cost by applying the correct rate, which is determined by both the job role and the period stored on a separate tab. I attempted to use a formula to achieve this, but it is currently returning an error.

Below is an example of what I have used - in this cell it returns a value of 18274.50 but should actually return a value of 3654.50.

=IF(J$12="","",J44*SUMIFS('Rate Detail'!$E:$E, 'Rate Detail'!$B:$B, 'Cost Sheet'!$B42,'Rate Detail'!$C:$C, 'Cost Sheet'!J$12))

J$12 is the date

J44 are the total hours

Rate Detail'!$E:$E are the hourly rate

Rate Detail'!$B:$B is the job role

Rate Detail'!$C:$C is the date

What would be a better method to calculate this cost?

Answer 1

This formula you are using

=IF(J$12="","",J44*SUMIFS('Rate Detail'!$E:$E, 'Rate Detail'!$B:$B, 'Cost Sheet'!$B42,'Rate Detail'!$C:$C, 'Cost Sheet'!J$12))

is interpreted as

when J12 is not blank, set it to the product of J44 and the sum of those values in column E for which all the following cells contain a non-zero value:

• The cell in the same row in column B.
• The cell in the same row in column C.
• The cell in column B of Cost Sheet that is 41 rows below the cell in column E.
• J12 (this is redundant).

Surely you did not mean to sum the hourly rates. If one job rate is 10 and another is 15 you wouldn't multiply the total hours by 25. Normally you compute the cost of the first job (multiply the hours spent by 10) and add to that the cost of the second job (multiply the time by 15).

You are summing over every cell in column E. Are there cells there that contain data other than an hourly rate?

What are you really trying to compute? The cost of one job? The cost of all jobs? The cost of work performed in a certain timeframe?

How is you data organized? Does Rate Detail have data for more than one job? Does Cost Sheet?

How can column C be the Hourly Rate when your picture shows Hourly rate at least four columns from the left edge? Please include the row and column headers in future pictures.

Answer 2

Hello @Peter_Holland,   

Thank you for posting your valuable question on Microsoft Q&A forum. 

To properly check what’s happening with your calculation, it would be most helpful if I could look directly at the workbook structure. 

You can upload the Excel file to your OneDrive and share it using an Anyone with the link can view link. Once you have the link, feel free to send it to me in our private message, and I’ll review it from there. 

---

Please reset this page and you'll see a pop up on top of your thread. You can click on View Messages to check what I've messaged you. This will be our chat channel and the data  stored in our private messages will automatically be deleted after 30 days.  

In the meantime, please take a look at the Period column in your Rate Detail sheet and confirm whether each Period value is stored as a real Excel date or text that looks like a date (e.g., “Dec-25”).

You can check this by clicking a Period cell and looking at the value in the formula bar. If Excel treats it as text, that would explain why the formula doesn’t match the date in your cost sheet.Looking forward to your response so we can get this sorted our together.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

It seems that the formula you are using is not calculating the cost correctly due to the way the SUMIFS function is being applied. The issue might stem from the fact that the SUMIFS function is summing all matching rates instead of returning a single rate that corresponds to the job role and date.

To fix this, you can modify your formula to ensure that it retrieves the correct hourly rate for the specified job role and date, and then multiply that rate by the total hours. Here’s a revised version of your formula:

=IF(J$12="","",J44*INDEX('Rate Detail'!$E:$E, MATCH(1, ('Rate Detail'!$B:$B='Cost Sheet'!$B42) * ('Rate Detail'!$C:$C='Cost Sheet'!J$12), 0)))

Explanation:

• INDEX: This function retrieves the hourly rate from the Rate Detail table.
• MATCH: This function finds the row where both the job role and the date match the criteria. The multiplication of the two conditions creates an array that MATCH can evaluate to find the first occurrence where both conditions are true.
• IF: This part remains the same, checking if the date cell is empty before performing the calculation.

This formula should return the correct cost based on the job role and the hours entered.

---

References:

• Determine cost rates for project-based estimates and actuals