adding text to a sumproduct formula

A

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
2:D8)
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?
 
J

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."),D2:D8),"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."),
2:D8
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
 
A

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."),D2:D8))&"
(%"&ROUND((VALUE(SUMPRODUCT(--(LEFT(C2:C8,4)="S.F."),E2:E8)+SUMPRODUCT(--(LEFT(C2
:C8,4)<>"S.F."),D2:D8))/TotalCost)*100, 1)&")"

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

For example,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top