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

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

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

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