Sumproduct with both number and text formats

[Curtis]

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

 

[Martin Fishlock]

try using -- on the numbers as
=SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$AS$4000))

 

[Curtis]

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

 

[Bob Phillips]

Maybe you need to coerce to a number

=IF(T1878="","",VLOOKUP(--T1878,Rates!$B$2:$F$237,3+ISNUMBER(MATCH($J1878,'Tech
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]

Thanks to all

Maybe you need to coerce to a number

=IF(T1878="","",VLOOKUP(--T1878,Rates!$B$2:$F$237,3+ISNUMBER(MATCH($J1878,'Tech
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)