Summing Numbers ending in letter
 

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, but 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,

bbkdude

Summing Numbers ending in letter
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

Summing Numbers ending in letter
 

Hi,

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

=TEXT(SUM(IF(RIGHT(H2:H370,1)="C",--LEFT(H2:H370,LEN(H2:H370)-1))),"#,##0C")

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


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2