SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec

T

TravisB

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=QBData_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!
 
D

Dave Peterson

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))
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

Dave, yes, I am searching on two criteria in a template (in the case below,
A13 and O4) and then looking in two columns in the datasheet (a separate
worksheet, QBData_Feb) to find data in a third. I then need to sum the
results of the findings in that third column where there are more multiple
results/values.

Your formula seems logical, but I keep getting an "n/a"...
 
T

TravisB

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_Feb!$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?
 
D

Dave Peterson

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))
Click to expand...

How about putting them back?
 
T

TravisB

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_Feb!$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?
 
T

TravisB

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.
 
D

Dave Peterson

Do you have any errors in those ranges?


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.
Click to expand...
 
T

TravisB

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
 
T

TravisB

btw, your formula definitely works if i limit this to the cells where there
are no errors like #n/a.

so now i just need to figure out how to adjust for the #n/a issue
 
D

Dave Peterson

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.


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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup Data based on 2 or more conditions/arguments 3
SUMPRODUCT question, finding data on multiple criteria 1
Index/match across multiple columns? 16
Index and Match 5
INDEX and MATCH 1
Index and Match with more than one Match 0
Index/Match for the largest value that matches 1
INDEX and sum 1

Top