adding text to a sumproduct formula

[Andy]

I have a sumproduct formula which I'd like to also display a percentage of the
total in the same cell. The formula is
SUMPRODUCT(--(LEFT(C2:C8,4)="S.F."),E2:E8)+SUMPRODUCT(--(LEFT(C2:C8,4)<>"S.F."),D
28)
If the result of this formula is 10 and the total for all the sumproduct formulas
in the same column is 100, the cell would display "10 (10%)" less the quotes
I've tried use "&" but my attempts either return an error or an empty cell. The
cell is located at F2 (for example) and the total is located at E50. Any ideas?

 

[Jonathan Cooper]

You need to convert the result of your formula into text. Once you do that, you can concatenate

For example

=TEXT(SUMPRODUCT(--(LEFT(C2:C8,4)="S.F."),E2:E8)+SUMPRODUCT(--(LEFT(C2:C8,4)<>"S.F."),D28),"0")&" (10)

----- Andy wrote: ----

I have a sumproduct formula which I'd like to also display a percentage of th
total in the same cell. The formula i
SUMPRODUCT(--(LEFT(C2:C8,4)="S.F."),E2:E8)+SUMPRODUCT(--(LEFT(C2:C8,4)<>"S.F."),
28
If the result of this formula is 10 and the total for all the sumproduct formula
in the same column is 100, the cell would display "10 (10%)" less the quote
I've tried use "&" but my attempts either return an error or an empty cell. Th
cell is located at F2 (for example) and the total is located at E50. Any ideas

 

[Andy]

Thanks Jonathan. Your suggestion led me to the solution so thank you. I used
the value function as I needed to manipulate the returned amount. This next line
is what I came up with and it works (just to close the thread for future searches
if any)

VALUE(SUMPRODUCT(--(LEFT(C2:C8,4)="S.F."),E2:E8)+SUMPRODUCT(--(LEFT(C2:C8,4)<>"S.
F."),D28))&"
(%"&ROUND((VALUE(SUMPRODUCT(--(LEFT(C2:C8,4)="S.F."),E2:E8)+SUMPRODUCT(--(LEFT(C2
:C8,4)<>"S.F."),D28))/TotalCost)*100, 1)&")"

You need to convert the result of your formula into text. Once you do that, you can concatenate.

For example,