Summing Numbers ending in letter

[bbkdude]

Hello All,

So here's what I am trying to accomplish:

1,500C
1,000C
2,500C
5,000C

My Sum is 10000C, but I would like it to read 10,000C with the comma.

Problem is my SUM formula removes the C, then sums, then adds the C, bu
not the 1,000 comma separator.

My SUM formula is, committed as an array:

{=SUM(IF((RIGHT(H2:H370)="C"),--LEFT(H2:H370,LEN(H2:H370)-1),""))&"C"}

Is there any way to make it so that it will keep the comma?

Regards,

bbkdud

 

[Claus Busch]

Hi,

Am Sat, 6 Jul 2013 14:39:20 +0100 schrieb bbkdude:

{=SUM(IF((RIGHT(H2:H370)="C"),--LEFT(H2:H370,LEN(H2:H370)-1),""))&"C"}

try:
=TEXT(SUM(IF(RIGHT(H2:H370,1)="C",--LEFT(H2:H370,LEN(H2:H370)-1))),"#,##0C")
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.

 

[bbkdude]

try:
=TEXT(SUM(IF(RIGHT(H2:H370,1)="C",--LEFT(H2:H370,LEN(H2:H370)-1))),"#,##0C")
and enter the array formula with CTRL+Shift+Enter

Wow, fantastic. That did the trick! Thank you very much

 

[Claus Busch]

Hi,

Am Sat, 6 Jul 2013 16:46:59 +0100 schrieb bbkdude:

or only:
=SUM(IF(RIGHT(H2:H370,1)="C",--LEFT(H2:H370,LEN(H2:H370)-1)))
and custom numberformat #,##0C


Regards
Claus B.