SUMPRODUCT and SUBSTITUTE

[gatgame]

I have the formula =SUMPRODUCT(SUBSTITUTE(0&B15:B80,"CA$","",1)+0,--($A$15:$A$80=$P$16))
it breaks #VALUE ! when it hits a cell in column B containing the string (CA$123).

Column B contains CA$1234 and (CA$123) values. I need the formula to SUBSTITUTE both if possible.

Thanks!

 

[pgc01]

Re: SUMPRODUCT and SUBSTITUTE help

Hi

I don't see any problem in the formula for the value CA$123. Maybe you have in the cell ca$123 and not CA$123?

Try just

=SUBSTITUTE(0&B15,"CA$","",1)

for that cell (amend the B15). What do you get?

 

[gatgame]

Re: SUMPRODUCT and SUBSTITUTE help

Thanks for the response! I found a workaround that seems to do the trick.