sum text and zero number in one cell

I would like to know how I can sum text, which show up in one cell and other cells are zero.
it looks like below

by condition is =IF(B$2=B3,C3,0)
=IF(B$2=B4,C4,0)....

after that, I want it to show only text on summarise cell, which is loacted at the bottom.

[TABLE="width: 192"]

[tr]

[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl24, width: 64, bgcolor: yellow, align: right"]1
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: left"]wdesd
[/TD]
[TD="bgcolor: transparent, align: left"]wdesd
[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: left"]s
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: left"]ds
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: left"]ds
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: left"]dsf
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent, align: left"]dsf
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: left"]dsf
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: left"]df
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="bgcolor: transparent, align: left"]dsf
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl25, bgcolor: transparent"][/TD]

[/tr]

[/TABLE]

Re: sum text and zero number in one cell

Quote from jproffer;602806

I can't speak for everyone else, but I can't figure out what you want to do. If you could post an example workbook with fake data (or the real data, if it's nothing secret, would be even better), that would help. Make one sheet showing your data as it is now...and a second worksheet showing what you want as a result after the formulas/VBA have done their thing.

Even a manually entered result will help us figure out what you want to do.

[TABLE="class: grid, width: 500, align: center"]

[tr]

[td]

Input number

[/td]

[td]

2

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

Music

[/td]

[td]

0

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

Comedy Movies

[/td]

[td]

Comedy Movies

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

Drama Movies

[/td]

[td]

0

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

I want Result of sum here is

[/td]

[td]

Comedy Movies

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[/TABLE]

When I input number 1, it means Music. Number 2 means Comedy Movies. Number 3 means Drama Movies.

After that, I want result of sum between 0 and text with the text that I choose only.
Because I will link it to another page result.

Thank you for you quick response.

Re: sum text and zero number in one cell

That's really helpful. Thank you for your excel formula.

Cheers,
Note S.