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