• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate sum based on Multiple sheets

R

rajesh2014

Member
Hi

I have a workbook with 3 sheets, Sheet1 i have columns Employee Name and Hours worked,

In Sheet2 i have hourly rate for each employees, columns Employee Name and Hourly Rate

In Sheet3, Output, i need to calculate what is the amount i need to pay each employee, IS that possible in Excel formulas.

I have attached sample file.
 

Attachments

sorry, Missed one small thing to above calculation, if needed i will create new post or else here only.

In my sheet1, i have one more column called Project, But this column is not in Sheet 2, so only column exist in both sheet is Employee Name, Suppose if i wanted to calculate Project like Total $ for Project1 , Project 2 ... etc.

Is that possible. I have attached sheet.
 

Attachments

thanks for your reply, something is missing, if i used the above formula in my sample excel i am getting, correct output if i used in my orginal file, i am not getting exact data, here is the formula i used :

=SUMPRODUCT(--(G16='Data '!A:A),'Data '!J:J,Translation!E:E)

Here G16 is Project name, In Data sheet column A:A my project list exist, Data J:J is the hourly worked detail, and in Translation E:E. $ per hour.

Is there any thing wrong with formula, my file is bit confidential, so i created dummy file.
 
I don't see any reason to go wrong if it is working in sample. Just check the secong and third array refference. Second array is hours worked and third array is rate/hour.

One thing to note is that both the project sheet and rate sheet should have same order of employee name.

Regards,
 
Thanks for your reply, Its working, but for defined range,

=SUMPRODUCT(IFERROR(LOOKUP(IF(Output!A2=Data!$A$2:$A$6,Data!$C$2:$C$6),Translation!$C$2:$C$6,Translation!$E$2:$E$6),0)*COUNTIF(Output!A2,Data!$A$2:$A$6)*Data!$J$2:$J$6)

I have undefined range, if i used as below its not working,

=SUMPRODUCT(IFERROR(LOOKUP(IF(Output!A2=Data!A:A,Data!C:C),Translation!C:C,Translation!E:E),0)*COUNTIF(Output!A2,Data!A:A)*Data!J:J)

Please advice
 
Last edited:
Why are using full columns in your calculation? It will slow down the process. What is the maximum number of rows in your database? Define a dynamic named range for your data set, by converting it to table.

Regards,
 
Problem is Data sheet will update daily, so we can't fix a defined range. I tried to create Column wise table, that also not worked.
 
You must log in or register to reply here.
Back
Top