Remember Me?

#1
July 6th 13, 02:39 PM
 Junior Member First recorded activity by ExcelBanter: Jul 2013 Posts: 3
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

#2
July 6th 13, 02:59 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,785
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
#3
July 6th 13, 04:46 PM
 Junior Member First recorded activity by ExcelBanter: Jul 2013 Posts: 3

Quote:
 Originally Posted by Claus Busch 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!
#4
July 6th 13, 05:07 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,785
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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post David New Users to Excel 5 July 25th 09 12:17 AM bigjackblack999 Excel Discussion (Misc queries) 3 July 15th 08 12:56 PM Cynthia Excel Worksheet Functions 3 July 7th 08 11:13 PM s Excel Discussion (Misc queries) 1 August 29th 07 04:56 PM Jan T. Excel Worksheet Functions 7 January 27th 07 09:45 PM

All times are GMT +1. The time now is 03:10 AM.