ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   sumproduct formula (https://www.excelbanter.com/excel-worksheet-functions/148711-sumproduct-formula.html)

Todd
sumproduct formula
 
Hi,

Help please? I need to match an item number on one page to an item number
on a second page and then select the base unit of measure. I have a table
that has item numbers and prices depending on the unit of measure. I want to
show the base unit of measure, in text, on the first page. But I can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box

Toppers
sumproduct formula
 
In your example, how do we know if unit of measure is "Yard" or "Box" for
item=10169 and Base=No. Or is this typo?

You probly need INDEX/Match Solution

=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Base"),0))

Enter with Ctrl+Shift+Enter


Should "Base" not be "Yes" or "No"?

I'm confused!

"Todd" wrote:

Hi,

Help please? I need to match an item number on one page to an item number
on a second page and then select the base unit of measure. I have a table
that has item numbers and prices depending on the unit of measure. I want to
show the base unit of measure, in text, on the first page. But I can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box

Toppers
sumproduct formula
 
Sorry ... mental aberration ....

try


=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Yes"),0))


"Toppers" wrote:

In your example, how do we know if unit of measure is "Yard" or "Box" for
item=10169 and Base=No. Or is this typo?

You probly need INDEX/Match Solution

=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Base"),0))

Enter with Ctrl+Shift+Enter


Should "Base" not be "Yes" or "No"?

I'm confused!

"Todd" wrote:

Hi,

Help please? I need to match an item number on one page to an item number
on a second page and then select the base unit of measure. I have a table
that has item numbers and prices depending on the unit of measure. I want to
show the base unit of measure, in text, on the first page. But I can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box

Todd
sumproduct formula
 
Thanks for the help! I am getting a NA error message. I don't see any
reason why but I have never done any multiple matches before. I made sure to
remove any hidden text and retyped the data but I keep getting the same
error. What can I do?

Thanks again,



Todd

"Toppers" wrote:

Sorry ... mental aberration ....

try


=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Yes"),0))


"Toppers" wrote:

In your example, how do we know if unit of measure is "Yard" or "Box" for
item=10169 and Base=No. Or is this typo?

You probly need INDEX/Match Solution

=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Base"),0))

Enter with Ctrl+Shift+Enter


Should "Base" not be "Yes" or "No"?

I'm confused!

"Todd" wrote:

Hi,

Help please? I need to match an item number on one page to an item number
on a second page and then select the base unit of measure. I have a table
that has item numbers and prices depending on the unit of measure. I want to
show the base unit of measure, in text, on the first page. But I can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box

Peo Sjoblom
sumproduct formula
 
Enter it with ctrl + shift & enter


--
Regards,

Peo Sjoblom



"Todd" wrote in message
...
Thanks for the help! I am getting a NA error message. I don't see any
reason why but I have never done any multiple matches before. I made sure
to
remove any hidden text and retyped the data but I keep getting the same
error. What can I do?

Thanks again,



Todd

"Toppers" wrote:

Sorry ... mental aberration ....

try


=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price
List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Yes"),0))


"Toppers" wrote:

In your example, how do we know if unit of measure is "Yard" or "Box"
for
item=10169 and Base=No. Or is this typo?

You probly need INDEX/Match Solution

=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price
List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Base"),0))

Enter with Ctrl+Shift+Enter


Should "Base" not be "Yes" or "No"?

I'm confused!

"Todd" wrote:

Hi,

Help please? I need to match an item number on one page to an item
number
on a second page and then select the base unit of measure. I have a
table
that has item numbers and prices depending on the unit of measure. I
want to
show the base unit of measure, in text, on the first page. But I
can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box

Toppers
sumproduct formula
 
NA usually indicates something doesn't match. Are you comparing like with
like e.g are BOTH the item numbers number OR text?

A small test I did worked OK.

If you want send s/sheet to:

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

HTH

"Todd" wrote:

Thanks for the help! I am getting a NA error message. I don't see any
reason why but I have never done any multiple matches before. I made sure to
remove any hidden text and retyped the data but I keep getting the same
error. What can I do?

Thanks again,



Todd

"Toppers" wrote:

Sorry ... mental aberration ....

try


=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Yes"),0))


"Toppers" wrote:

In your example, how do we know if unit of measure is "Yard" or "Box" for
item=10169 and Base=No. Or is this typo?

You probly need INDEX/Match Solution

=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Base"),0))

Enter with Ctrl+Shift+Enter


Should "Base" not be "Yes" or "No"?

I'm confused!

"Todd" wrote:

Hi,

Help please? I need to match an item number on one page to an item number
on a second page and then select the base unit of measure. I have a table
that has item numbers and prices depending on the unit of measure. I want to
show the base unit of measure, in text, on the first page. But I can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box

Todd
sumproduct formula
 
Thanks Toppers. I did a seperate test and the formula worked great. But not
in that workbook. I am looking at cell formats but do not see anything. So
I sent a sample. Thanks very much.


Todd

"Toppers" wrote:

NA usually indicates something doesn't match. Are you comparing like with
like e.g are BOTH the item numbers number OR text?

A small test I did worked OK.

If you want send s/sheet to:

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

HTH

"Todd" wrote:

Thanks for the help! I am getting a NA error message. I don't see any
reason why but I have never done any multiple matches before. I made sure to
remove any hidden text and retyped the data but I keep getting the same
error. What can I do?

Thanks again,



Todd

"Toppers" wrote:

Sorry ... mental aberration ....

try


=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Yes"),0))


"Toppers" wrote:

In your example, how do we know if unit of measure is "Yard" or "Box" for
item=10169 and Base=No. Or is this typo?

You probly need INDEX/Match Solution

=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Base"),0))

Enter with Ctrl+Shift+Enter


Should "Base" not be "Yes" or "No"?

I'm confused!

"Todd" wrote:

Hi,

Help please? I need to match an item number on one page to an item number
on a second page and then select the base unit of measure. I have a table
that has item numbers and prices depending on the unit of measure. I want to
show the base unit of measure, in text, on the first page. But I can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box

Toppers
sumproduct formula
 
Nothing received as yet ......13:17 PST.

"Todd" wrote:

Thanks Toppers. I did a seperate test and the formula worked great. But not
in that workbook. I am looking at cell formats but do not see anything. So
I sent a sample. Thanks very much.


Todd

"Toppers" wrote:

NA usually indicates something doesn't match. Are you comparing like with
like e.g are BOTH the item numbers number OR text?

A small test I did worked OK.

If you want send s/sheet to:

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

HTH

"Todd" wrote:

Thanks for the help! I am getting a NA error message. I don't see any
reason why but I have never done any multiple matches before. I made sure to
remove any hidden text and retyped the data but I keep getting the same
error. What can I do?

Thanks again,



Todd

"Toppers" wrote:

Sorry ... mental aberration ....

try


=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Yes"),0))


"Toppers" wrote:

In your example, how do we know if unit of measure is "Yard" or "Box" for
item=10169 and Base=No. Or is this typo?

You probly need INDEX/Match Solution

=INDEX('Price List'!$D$2:$D$500,MATCH(1,('Price List'!$A$2:$A$500=A5)*('Price
List'!$C$2:$C$500="Base"),0))

Enter with Ctrl+Shift+Enter


Should "Base" not be "Yes" or "No"?

I'm confused!

"Todd" wrote:

Hi,

Help please? I need to match an item number on one page to an item number
on a second page and then select the base unit of measure. I have a table
that has item numbers and prices depending on the unit of measure. I want to
show the base unit of measure, in text, on the first page. But I can't
figure out how to do that.

I am trying to use a sumproduct formula to return text. And it isn't
working. Thanks,

Todd

=SUMPRODUCT(--('Price List'!$A$2:$A$500=A5),--('Price
List'!$C$2:$C$500="Base"),TEXT('Price List'!$D$2:$D$500,0))

Price List
Item Number Base Unit of Measure
10169 yes pound
10169 no yard
10169 no box


All times are GMT +1. The time now is 11:56 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com