I'm using an IFError in my SumProduct Formulas because there are text values in the array. This is working for my number values, but I want the sumproducts of cells with text values to show a text result. Can't figure out how.

I'm using the following SumProduct formula in P55 through P65 on a summary sheet. This is the formula for P55, but it changes accordingly as you work down to P65. There is a lot going on here, but basically D55:L55 are input cells that are used to match up with D61:D75 on the '11 - Chart - Investments. It basically allows me to combine multiple individual line items into summary line items by picking which items I want included.

=IF(And($D55="",$F55="",$H55="",$J55="",$L55),"",SUMPRODUCT(IFerror(ISNUMBER(SEARCH("," & '11 - Chart - Investments'!$D$61:$D$75  & ",","," & $D55&","&$F55&","&$H55&","&$J55&","&$L55 & ","))*'11 - Chart - Investments'!F$61:F$75)))

The cells in the '11 - Chart - Investments'!F$61:F$75 are either blank, a percentage value, or have the text "Allowance". Because of "Allowance", I added the IFError into the formula since if didn't have that it would result in a #Value error. This is currently working for the percentage cells in the range, but when I get to "Allowance" cells, it just gives me "0.00%".

If I try to modify so that the value IF Error is "Allowance" like so, '11 - Chart - Investments'!F$61:F$75, "Allowance"))), it still results in a "0.00%".

If I try to modify it so that the IfError comes before the SumProduct and the value if error is "Allowance", the sumproducts for percentages that are currently working correctly also become "Allowance".

Any ideas? By the way, the F61:F75 are constantly changing between blank, percentages, and "Allowance". That's why I need the formula. If that weren't the case, I would just hardcode the "Allowance" in each time.