Formula returns text that I cannot sum and cannot convert to number.

[Patrickcabo]

Hello, I have a formula using CONCATENATE because I have more than 7 nested ifs. This formula returns the right answer but I cannot sum the result of the formula. The answer returns in text and I don't know how to convert it to a number. I tried replacing all the "" in the FALSE part of the if functions but that did not do it.

Thanks for any comments. Formula below.


=CONCATENATE(IF($BQ11=3,CB11*$BU11,""),IF($BQ11=4,CC11*$BU11,""),IF($BQ11=5,CD11*$BU11,""),IF($BQ11=6,CE11*$BU11,""),IF($BQ11=7,CF11*$BU11,""),IF($BQ11=8,CG11*$BU11,""),IF($BQ11=9,CH11*$BU11,""),IF($BQ11=10,CI11*$BU11,""),IF($BQ11=11,CJ11*$BU11,""),IF($BQ11=12,CK11*$BU11,""),IF($BQ11=13,CL11*$BU11,""),IF($BQ11=14,CM11*$BU11,""))

 

[Marcelo Branco]

If I am not mistaken, I believe this even simple formula will work...

=CU11*INDEX(CB11:CM11,1,BQ11-2)

Rick, you are right!
Just small adjustments:
=IFERROR(BU11*INDEX(CB11:CM11,BQ11-2),"")

M.