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

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

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

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

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