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(D1619,"-","")))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

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(D1619,"-",""))

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