Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Find the combination of numbers that when added equal a reqired total?? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel |