SUMPRODUCT vs Text???
 

Excel 2000 ... TS = TabSheet ... :)

TS1 ... Range C2:C10000 ... Text Data
TS1 ... Range D2:D10000 ... Text Data

TS2 ... Range B2:B6000 ... Text Data
TS2 ... Range F2:F6000 ... Text Data


TS1 ... Range E2:E10000 ... I Need Formula ... If value in
(TS2 B2:B6000 matches value in TS1 C2:C10000) and value in
(TS2 F2:F6000 matches value in TS1 D2:D10000) place value
from TS2 Col O into TS1 Col E.

I accomplished this with INDEX & MATCH, but calculation is
ever so slow ... That said ... I thought I could do it
with SUMPRODUCT ... but I am having an issue appropriately
writing SUMPRODUCT formula ... Most likely it is a short-
coming on my part, but I can't seem to get SUMPRODUCT
Formula to match, capture & return the value I need. I am
thinking it is because the data is "TEXT" & I am trying to
use the Cell Locations in my formula rather than hard TEXT
enclosed in quotes ("TEXT")...

Above said ... I am now turning to the many Excel
Magicians that support this board ... Thanks ... Kha

Hi Ken

AFAIK you can't use sumproduct for this, sumproduct returns either sum &
product of ranges (traditional use) or works as a multi-conditional SUMIF or
COUNTIF (current popular use - check out
..http://www.xldynamic.com/source/xld.SUMPRODUCT.html.. for more details)

IMHO the simplest way to accomplish what you want to do is to insert in TS2
a new column (column A that contains the formula
=B2&"/"&F2 - using their current cell references in this example - this
column can be hidden) ... this will allow you to use VLOOKUP to return the
information from TS2 column O to TS1 column E

the VLOOKUP function in this case would be
=VLOOKUP(C1&"/" &D1,Sheet2!$A$2:$O$6000,15,0)

and to deal with the value not being found
=IF(ISNA(VLOOKUP(C1&"/" &D1,Sheet2!$A$2:$O$6,15,0)),"",VLOOKUP(C1&"/"
&D1,Sheet2!$A$2:$O$6,15,0))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Ken" wrote in message
...
Excel 2000 ... TS = TabSheet ... :)

TS1 ... Range C2:C10000 ... Text Data
TS1 ... Range D2:D10000 ... Text Data

TS2 ... Range B2:B6000 ... Text Data
TS2 ... Range F2:F6000 ... Text Data


TS1 ... Range E2:E10000 ... I Need Formula ... If value in
(TS2 B2:B6000 matches value in TS1 C2:C10000) and value in
(TS2 F2:F6000 matches value in TS1 D2:D10000) place value
from TS2 Col O into TS1 Col E.

I accomplished this with INDEX & MATCH, but calculation is
ever so slow ... That said ... I thought I could do it
with SUMPRODUCT ... but I am having an issue appropriately
writing SUMPRODUCT formula ... Most likely it is a short-
coming on my part, but I can't seem to get SUMPRODUCT
Formula to match, capture & return the value I need. I am
thinking it is because the data is "TEXT" & I am trying to
use the Cell Locations in my formula rather than hard TEXT
enclosed in quotes ("TEXT")...

Above said ... I am now turning to the many Excel
Magicians that support this board ... Thanks ... Kha

I think sumproduct can work here.

In Sheet 1, cell H2: =C2&"/"&D2 (copy down)

Number cells G2 to G1000 from 1 to 999 in Sheet 2.

In Sheet 1, cell I2:
=SUMPRODUCT(--(Sheet2!$A$2:$A$1000=H2),Sheet2!$G$2:$G$1000)
(copy down)

In cell Sheet 1, cell E2: =IF(I2<0,VLOOKUP(I2,Sheet2!$G$2:$O$1000,9),"")
(copy down)

"JulieD" wrote:

Hi Ken

AFAIK you can't use sumproduct for this, sumproduct returns either sum &
product of ranges (traditional use) or works as a multi-conditional SUMIF or
COUNTIF (current popular use - check out
..http://www.xldynamic.com/source/xld.SUMPRODUCT.html.. for more details)

IMHO the simplest way to accomplish what you want to do is to insert in TS2
a new column (column A that contains the formula
=B2&"/"&F2 - using their current cell references in this example - this
column can be hidden) ... this will allow you to use VLOOKUP to return the
information from TS2 column O to TS1 column E

the VLOOKUP function in this case would be
=VLOOKUP(C1&"/" &D1,Sheet2!$A$2:$O$6000,15,0)

and to deal with the value not being found
=IF(ISNA(VLOOKUP(C1&"/" &D1,Sheet2!$A$2:$O$6,15,0)),"",VLOOKUP(C1&"/"
&D1,Sheet2!$A$2:$O$6,15,0))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Ken" wrote in message
...
Excel 2000 ... TS = TabSheet ... :)

TS1 ... Range C2:C10000 ... Text Data
TS1 ... Range D2:D10000 ... Text Data

TS2 ... Range B2:B6000 ... Text Data
TS2 ... Range F2:F6000 ... Text Data


TS1 ... Range E2:E10000 ... I Need Formula ... If value in
(TS2 B2:B6000 matches value in TS1 C2:C10000) and value in
(TS2 F2:F6000 matches value in TS1 D2:D10000) place value
from TS2 Col O into TS1 Col E.

I accomplished this with INDEX & MATCH, but calculation is
ever so slow ... That said ... I thought I could do it
with SUMPRODUCT ... but I am having an issue appropriately
writing SUMPRODUCT formula ... Most likely it is a short-
coming on my part, but I can't seem to get SUMPRODUCT
Formula to match, capture & return the value I need. I am
thinking it is because the data is "TEXT" & I am trying to
use the Cell Locations in my formula rather than hard TEXT
enclosed in quotes ("TEXT")...

Above said ... I am now turning to the many Excel
Magicians that support this board ... Thanks ... Kha