sumproduct - add two cells together and then multiply by the value of a cell off another sheet

Dear @KARL HEINE,

Thank you for posting your question in the Microsoft Q&A forum!

According to your description, you do not need to use SUMPRODUCT in this scenario. A straightforward formula will be clearer, easier to understand, and simpler to maintain

From what’s visible in your screenshots:

J = OUT

K = IN

L = Billing result

The rate is on the BILLING sheet:

• Column A = Billing code (e.g. 4209701001)
• Column B = Rate (e.g. 0.050785130)

Column L header on EMPLOYEE sheet contains the billing code (4209701001)

=(J3+K3)*VLOOKUP(L$2,BILLING!$A:$B,2,FALSE)

• J3 + K3: Adds OUT and IN calls 4 + 2 = 6

VLOOKUP(L$2, BILLING!$A:$B, 2, FALSE)

• Looks up billing code 4209701001 on the BILLING sheet > returns 0.050785130

Final result: 6 × 0.050785130 = 0.304710780

If you prefer XLOOKUP:

=(J3+K3)*XLOOKUP(L$2,BILLING!$A:$A,BILLING!$B:$B)

I hope this helps! Please feel free to reply below if you have any update or further concern.  

Your understanding and co-operation are highly appreciated. Thanks for your precious time. Have a nice day!

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

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.

Use a simple multiplication of the sum of J and K by the rate looked up on the Billing sheet.

Assuming on sheet DB01:

• Employee row is 3
• OUT is in J3
• IN is in K3
• The code in L2 (on DB01) exists in column A of sheet Billing, with the rate in column B

In L3 enter:

=(J3+K3)*VLOOKUP($L$2,Billing!$A:$B,2,FALSE)

Explanation:

• J3+K3 adds OUT and IN calls (4+2 = 6 in the example).
• VLOOKUP($L$2,Billing!$A:$B,2,FALSE) returns the rate from column B on Billing that matches the code in L2.
• The result is 6 * 0.050785130 = 0.30471078.

Copy the formula down column L for other employees as needed.

References:

• Use SUMPRODUCT to sum the product of corresponding values in one or more arrays
• SUMPRODUCT function
• Perform conditional calculations on ranges of cells
• Multiply and divide numbers in Excel
• Use Excel as your calculator in Excel for Mac
• Examples of common formulas in lists
• Create Formulas for Calculations in Power Pivot
• How to correct a #VALUE! error in the SUMPRODUCT function
• combining multiple formulas in one cell (not an if statement, does multiple formulas to create one answer) - Microsoft Q&A
• SUMIF returning #N/A-Function Argument returns FALSE when TRUE ??? - Microsoft Q&A
• Adding an equation when using join and sum. - Microsoft Q&A
• Promlem with ran out resources when canculation in excel - Microsoft Q&A
• XLOOKUP, FILTER NOT WORKING WITH EXCEL ON MAC 365. - Microsoft Q&A