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!

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

"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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

"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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple
 

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

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