Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 6th 13, 02:39 PM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2013
Posts: 3
Default 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   Report Post  
Old July 6th 13, 02:59 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,685
Default 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   Report Post  
Old July 6th 13, 04:46 PM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2013
Posts: 3
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I count the cells ending with a specific letter? David New Users to Excel 5 July 25th 09 12:17 AM
filtering for numbers ending in 000 bigjackblack999 Excel Discussion (Misc queries) 3 July 15th 08 12:56 PM
formula ending with a letter Cynthia Excel Worksheet Functions 3 July 7th 08 11:13 PM
How do you add three ending '0's to a column of numbers? s Excel Discussion (Misc queries) 1 August 29th 07 04:56 PM
Summing all cells containing the letter K Jan T. Excel Worksheet Functions 7 January 27th 07 09:45 PM


All times are GMT +1. The time now is 10:51 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017