Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Sumproduct return Text values | Excel Worksheet Functions | |||
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
Return text using Sumproduct | Excel Worksheet Functions | |||
Quick Sumproduct vs Text return | Excel Discussion (Misc queries) | |||
Sumproduct to return a text | Excel Worksheet Functions |