Multiple Criteria on Job Costings issue

Hi,

I'm slightly simplify my approach to my job costing problem, from what I was originally trying to do (which was way to complicated and probably needs a full program itself to work) to the below.

I have the attached example spreadsheet that I am trying to calculate wage costings to per day and per job.

The "QLD WSheet" tab calculates a average hourly rate per day (and the formula works based on hour many hours in a day the person has worked, multiplied by their rates specifics on a separate sheet (which all works fine)) - I then want to pull this value across to the "QLD Timesheet" tab to multiple the average hourly rate for the day by the total of Std and Overtime Hrs for that person on that day and job.

What I have tried is the below formula:
=SUMPRODUCT(SUMIFS(INDEX('QLD WSheet'!1:1048576,0,MATCH('QLD Timesheet'!D7,'QLD WSheet'!D2:AS2,0)),'QLD WSheet'!C4:C31,'QLD Timesheet'!E7))
but I only get a #VALUE! result.

I have tried multiple ways including the Array Ctrl + Shift + Enter function which still just gives me the same result.

Appreciate any help offered to fix my dilemma.

Matt

The MATCH part of the formula returns 3 (column F). The third column of 1:1048576 is column C. Since the formula INDEX references whole columns 'QLD WSheet'!C4:C31 needs to be 'QLD WSheet'!C:C. Try

Formula:

=SUMPRODUCT(SUMIFS(INDEX('QLD WSheet'!D:AS,0,MATCH('QLD Timesheet'!D7,'QLD WSheet'!D2:AS2,0)),'QLD WSheet'!C:C,'QLD Timesheet'!E7))

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

You are welcome. Thank you for the feedback and re-titling the thread.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.