Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
mikeburg
 
Posts: n/a
Default Formula to sum total numbers with dashes in them


Need formula in cell D20 to sum total numbers with dashes in them. For
example:

In cells D7 thru D16
D16 | 450-33-6320
D17 | 83-0381086
D18 | 459-15-6027
D19 | 75-2708781

In cell D20 | 2,492,582,214 Control total

Thanks a million, mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=505522

  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Formula to sum total numbers with dashes in them

=SUMPRODUCT(--(SUBSTITUTE(D16:D19,"-","")))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mikeburg" wrote in
message ...

Need formula in cell D20 to sum total numbers with dashes in them. For
example:

In cells D7 thru D16
D16 | 450-33-6320
D17 | 83-0381086
D18 | 459-15-6027
D19 | 75-2708781

In cell D20 | 2,492,582,214 Control total

Thanks a million, mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:

http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=505522



  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default Formula to sum total numbers with dashes in them

On Thu, 26 Jan 2006 16:00:37 -0600, mikeburg
wrote:


Need formula in cell D20 to sum total numbers with dashes in them. For
example:

In cells D7 thru D16
D16 | 450-33-6320
D17 | 83-0381086
D18 | 459-15-6027
D19 | 75-2708781

In cell D20 | 2,492,582,214 Control total

Thanks a million, mikeburg


The **array** formula:

=SUM(--SUBSTITUTE(D16:D19,"-",""))

To enter an array formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

If blank cells may be in the range, then use this *array* formula instead:

=SUM(--SUBSTITUTE(0 & rng, "-",""))


--ron
  #4   Report Post  
Posted to microsoft.public.excel.misc
mikeburg
 
Posts: n/a
Default Formula to sum total numbers with dashes in them


Thanks so very much. I wish I was as good as you guys! mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=505522

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
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Find the combination of numbers that when added equal a reqired total?? Handsy11 Excel Worksheet Functions 5 July 12th 05 04:55 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Creating a check mark box MarthaSue Setting up and Configuration of Excel 18 April 28th 05 12:31 AM


All times are GMT +1. The time now is 06:38 PM.

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

About Us

"It's about Microsoft Excel"