need sumproduct to return text values...
 

this is a follow up to my previously posted subject line: "vlookup (indirect,
or sumproduct) using three tabs"

i have a sumproduct working (sort of, but not quite.) sumproduct ignores
text and only returns numerical values, or 0 if no match is found or if
there is text. how can i change the following to accept the text that is
displayed or display "Invalid item"

=IF(A12="","",SUMPRODUCT(--(VLOOKUP(H5,Suppliers!A:B,2,FALSE)=Items!A:A),--(Items!B:B='Purchase Order (Inventory)'!A12),Items!C:C))

this is the first part of the problem. i will work on the second part to
see if i can atleast get part of the answer myself, if not, i'll be back.

thank you,

jat

need sumproduct to return text values...
 

Prior to XL-2007, you can not use a whole column for SUMPRODUCT, you have to
give it a range ex. B1:B100


"jat" wrote:

this is a follow up to my previously posted subject line: "vlookup (indirect,
or sumproduct) using three tabs"

i have a sumproduct working (sort of, but not quite.) sumproduct ignores
text and only returns numerical values, or 0 if no match is found or if
there is text. how can i change the following to accept the text that is
displayed or display "Invalid item"

=IF(A12="","",SUMPRODUCT(--(VLOOKUP(H5,Suppliers!A:B,2,FALSE)=Items!A:A),--(Items!B:B='Purchase Order (Inventory)'!A12),Items!C:C))

this is the first part of the problem. i will work on the second part to
see if i can atleast get part of the answer myself, if not, i'll be back.

thank you,

jat

need sumproduct to return text values...
 

sorry, using office 2007. i just tried it with a range (just in case) but
still same results - ignores text, returning 0.

jat


"Teethless mama" wrote:

Prior to XL-2007, you can not use a whole column for SUMPRODUCT, you have to
give it a range ex. B1:B100


"jat" wrote:

this is a follow up to my previously posted subject line: "vlookup (indirect,
or sumproduct) using three tabs"

i have a sumproduct working (sort of, but not quite.) sumproduct ignores
text and only returns numerical values, or 0 if no match is found or if
there is text. how can i change the following to accept the text that is
displayed or display "Invalid item"

=IF(A12="","",SUMPRODUCT(--(VLOOKUP(H5,Suppliers!A:B,2,FALSE)=Items!A:A),--(Items!B:B='Purchase Order (Inventory)'!A12),Items!C:C))

this is the first part of the problem. i will work on the second part to
see if i can atleast get part of the answer myself, if not, i'll be back.

thank you,

jat