Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with both number and text formats
I currently use the following formula
=SUMPRODUCT(($J$5:$J$4000=$T$4007)*($AS$5:$AS$4000 <0)*($AS$5:$AS$4000)) But it does not seems to work if the value in column J is formated as text ( ex 3644 or '3644) Is there a way to modify the formula so that it does not matter if the value in column J is foramted as a number or text? Thanks ce |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with both number and text formats
try using -- on the numbers as
=SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Curtis" wrote: I currently use the following formula =SUMPRODUCT(($J$5:$J$4000=$T$4007)*($AS$5:$AS$4000 <0)*($AS$5:$AS$4000)) But it does not seems to work if the value in column J is formated as text ( ex 3644 or '3644) Is there a way to modify the formula so that it does not matter if the value in column J is foramted as a number or text? Thanks ce |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with both number and text formats
Thanks
I have another question of a simular nature. I also use the formula below but run into the same problem when the value in T1878 is formated as text. =IF(T1878="","",VLOOKUP(T1878,Rates!$B$2:$F$237,3+ ISNUMBER(MATCH($J1878,'Tech ID''s'!$B$2:$B$10,0)),0)) Any ideas Thanks ce "Martin Fishlock" wrote: try using -- on the numbers as =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Curtis" wrote: I currently use the following formula =SUMPRODUCT(($J$5:$J$4000=$T$4007)*($AS$5:$AS$4000 <0)*($AS$5:$AS$4000)) But it does not seems to work if the value in column J is formated as text ( ex 3644 or '3644) Is there a way to modify the formula so that it does not matter if the value in column J is foramted as a number or text? Thanks ce |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with both number and text formats
Maybe you need to coerce to a number
=IF(T1878="","",VLOOKUP(--T1878,Rates!$B$2:$F$237,3+ISNUMBER(MATCH($J1878,'T ech ID''s'!$B$2:$B$10,0)),0)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curtis" wrote in message ... Thanks I have another question of a simular nature. I also use the formula below but run into the same problem when the value in T1878 is formated as text. =IF(T1878="","",VLOOKUP(T1878,Rates!$B$2:$F$237,3+ ISNUMBER(MATCH($J1878,'Tech ID''s'!$B$2:$B$10,0)),0)) Any ideas Thanks ce "Martin Fishlock" wrote: try using -- on the numbers as =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Curtis" wrote: I currently use the following formula =SUMPRODUCT(($J$5:$J$4000=$T$4007)*($AS$5:$AS$4000 <0)*($AS$5:$AS$4000)) But it does not seems to work if the value in column J is formated as text ( ex 3644 or '3644) Is there a way to modify the formula so that it does not matter if the value in column J is foramted as a number or text? Thanks ce |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with both number and text formats
Thanks to all
"Bob Phillips" wrote: Maybe you need to coerce to a number =IF(T1878="","",VLOOKUP(--T1878,Rates!$B$2:$F$237,3+ISNUMBER(MATCH($J1878,'T ech ID''s'!$B$2:$B$10,0)),0)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curtis" wrote in message ... Thanks I have another question of a simular nature. I also use the formula below but run into the same problem when the value in T1878 is formated as text. =IF(T1878="","",VLOOKUP(T1878,Rates!$B$2:$F$237,3+ ISNUMBER(MATCH($J1878,'Tech ID''s'!$B$2:$B$10,0)),0)) Any ideas Thanks ce "Martin Fishlock" wrote: try using -- on the numbers as =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Curtis" wrote: I currently use the following formula =SUMPRODUCT(($J$5:$J$4000=$T$4007)*($AS$5:$AS$4000 <0)*($AS$5:$AS$4000)) But it does not seems to work if the value in column J is formated as text ( ex 3644 or '3644) Is there a way to modify the formula so that it does not matter if the value in column J is foramted as a number or text? Thanks ce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with either text of number format | Excel Worksheet Functions | |||
Custom number formats in TEXT function | Excel Worksheet Functions | |||
Text, Number and Date formats excel 2003 driving experienced user | Excel Discussion (Misc queries) | |||
Sumproduct with different number formats | Excel Discussion (Misc queries) | |||
Can you retain different text formats when merging text? | Excel Discussion (Misc queries) |