| Home |
| Search |
| Today's Posts |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
Glad you got it working.
TravisB wrote: Dave, you are the man. That did the trick. Thanks a ton for your patience! "Dave Peterson" wrote: If you those n/a's show up because of formulas, maybe you could hide them with a change to your formula: =if(isna(yourformula),"",yourformula) Or if you want to keep an indicator: =if(isna(yourformula),"Missing",yourformula) If this isn't possible, which column has the errors, B, C, or I? The =sumproduct() formula may be able to be replaced with an equivalent array formula. TravisB wrote: yep, there are some #n/a results in the core data where it has to do a vlookup and in some instances finds no results i could try to remove these, but it would largely defeat the purpose of trying to automate the process i tried to turn error checking off, but that didn't seem to help "Dave Peterson" wrote: Do you have any errors in those ranges? TravisB wrote: sorry dave, i thought those were formatting errors in the usenet community. i put them back in but i'm still getting an n/a. i've tried hitting just ENTER and CTRL+SHIFT+ENTER to make it an array. No luck with either. "Dave Peterson" wrote: My formula had some -- in it that yours didn't. =sumproduct(--(QBData_Feb!$B$4:$B$500=$a13), --(QBData_Feb!$C$4:$C$500=o$4), ($i$4:$i$500)) How about putting them back? TravisB wrote: Dave, yes, that seems correct. Here's a sample of the data: Inv School Category Category Qty Amount CLE430 Clemson University 03C CLE 430 430 2 29.5 CLE440 Clemson University 03A CLE 440 440 1 15 CLE460 Clemson University 03B CLE 460 460 1 9.75 CLE610 Clemson University 07D CLE 610 610 1 9.75 FSU210 Florida State 04A FSU 210 210 1 6.5 FSU410 Florida State 07D FSU 410 410 1 15 FSU610 Florida State 07D FSU 610 610 1 10 So I'm searching on two columns (what would be ColB and ColC above which are unlabeled) and then trying to return the sum from ColH (Amount). I adjusted your recommended formula to this: =SUMPRODUCT((QBData_Feb!$B$4:$B$500=$A7),(QBData_F eb!$C$4:$C$500=E$4),(QBData_Feb!$I$4:$I$500)) As I understand it, this would look for the data inputs in A7 and E4 on the template spreadsheet, go look in columns B and C of my data spreadsheet, and then sum any values it finds for the two from column I. Your sumproduct formula makes sense to me, but I'm getting an "n/a" even though I know there's data there to return. Any thoughts? "Dave Peterson" wrote: So you're trying to match on two columns and add up the values in the third? =sumproduct(--(QBData_Feb!$B$4:$B$500=$a13), --(QBData_Feb!$C$4:$C$500=o$4), ($i$4:$i$500)) TravisB wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| 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 | |||
Threaded Mode
