Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec
Hi, I am still having an issue with a somewhat complex data retrieval/lookup
process. Thanks to those who have tried to help. I am setting up a Workbook that tracks royalty reports for 3 dozen licensees we work with. I am outputting data from our accounting system to a worksheet and I want to look up the sales revenue for each licensee and copy/retrieve that data into the template. The problem is, there are multiple licensees with multiple product types, and in some cases, the same product type is listed more than once for a given licensee: I have tried doing this with an INDEX/MATCH that is "mostly" working: {=INDEX(QBData_Feb!$I$4:$I$500,MATCH(1,($A13=QBDat a_Feb!$B$4:$B$500)*(O$4=QBData_Feb!$C$4:$C$500),0) )} The problem is, the index/match is only returning the first value it finds for the specified condition when often there are several instances where more than one value needs to be found. In the above example, $A13 and O$4 can be found in the data table more than once, so I need those records summed. My data looks like this: DataSheet: QBData_Feb DataRange: QBData_Feb!A:I Lookup/Template Sheet: Royalty Report (Feb07) Lookup Criteria1: various licensees in column A Lookup Criteria2: various product types in row 4 Any thoughts on how I can return the sum of several records would be appreciated. Thanks! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Multiple < Conditions | Excel Worksheet Functions | |||
How do I add multiple values that match multiple conditions? | Excel Discussion (Misc queries) | |||
Index/Match Multiple Criteria | Excel Discussion (Misc queries) | |||
Multiple SumProduct conditions | Excel Worksheet Functions | |||
Sumproduct Multiple Conditions | Excel Worksheet Functions |