Formula to sum total numbers with dashes in them

[mikeburg]

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

[Bob Phillips]

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ron Rosenfeld]

On Thu, 26 Jan 2006 16:00:37 -0600, mikeburg
<[email protected]> 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

[mikeburg]

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