Summing Numbers ending in letter

B

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
 
C

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"}
Click to expand...

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.
 
B

bbkdude

Claus said:
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
Click to expand...

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

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.
 
Top