BKennedy2008
asked on
Easy Excel formula needed
I need a formula to sum the total payments for each customer by looking up the invoice on the payments tab. My Sum with vlookup only finds the first payment and returns that amount
Sheet1
Invoice Invoice Amount Total Paid Balance Due
10 $500 * Formula Needed B2-C2
11 $325
Sheet2 (Payments)
Invoice Payment
10 $50
10 $65
11 $30
11 $45
Sheet1
Invoice Invoice Amount Total Paid Balance Due
10 $500 * Formula Needed B2-C2
11 $325
Sheet2 (Payments)
Invoice Payment
10 $50
10 $65
11 $30
11 $45
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, Now I know
Alternative approach:
Sheet1:
Invoice Amount
10 $500
11 $325
Sheet2:
Invoice Amount
10 -$50
10 -$65
11 -$30
11 -$45
You can then use the Consolidate feature to combine the sheets into one sheet and then create a Pivot Table on the new sheet and it will show the balance per invoice because it will sum the amounts per invoice.
Sheet1:
Invoice Amount
10 $500
11 $325
Sheet2:
Invoice Amount
10 -$50
10 -$65
11 -$30
11 -$45
You can then use the Consolidate feature to combine the sheets into one sheet and then create a Pivot Table on the new sheet and it will show the balance per invoice because it will sum the amounts per invoice.
Sumif.xlsx