Is there a way to lookup/match multiple instances of a match and SUM them up?
I am trying to lookup a number (invoice amount in dollars) on another worksheet by matching WorkNumber, then get the InvoiceAmount and SUM the amounts. The catch is that on the 2nd sheet (invoice data), there can be more than one row with the same WorkNumber but different invoice amounts.
To better explain:
- The spreadsheet has worksheets "WorkAuth" and "InvoiceData"
- "WorkAuth" sheet has WorkNumber (col A)
- "InvoiceData" sheet has WorkNumber (col F) and InvoiceAmount (col I)
- "InvoiceAmount" is not unique in "InvoiceData" sheet because there can be more than one invoice for a WorkNumber
- I need to add a column in "WorkAuth" sheet that has the total amount invoiced to date.
My current formula in the "WorkAuth" sheet is:
=SUM(INDEX('Pre Invoice Data'!I:I,MATCH(A2,'Pre Invoice Data'!F:F,0)))
but MATCH stops when it finds the first match. So for cases where there is more that 1 invoice on a WorkAuth, my SUM only using the 1st instance of InvoiceAmount which is not correct because it's not finding all of the InvoiceAmounts
Looks like SUM/INDEX/MATCH is the wrong set of functions to do this. Can someone tell me what function(s) to use?
I am using Excel 2016.
