I have a column of data containing dollar amounts, as well as a record count, within brackets, in each cell. (see below)
I can extract the dollar amounts and record counts individually with formulas easily enough but I haven't been able to figure out a formula to sum the entire column. I suspect an array formula is the ticket but I've had no luck figuring it out. Any suggestions greatly appreciated!
I have attached a sample file with the below example as well. Thank you
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 55"] Month[/TD]
[TD="width: 228"]Total Assigned[/TD]
[TD="width: 184"][SIZE=9px]=VALUE(TRIM(LEFT(C3,FIND("(",C3)-1)))[/SIZE][/TD]
[TD="width: 663, colspan: 2"][SIZE=9px]=VALUE(LEFT(MID(C8,FIND("(",C8)+1,30),LEN(MID(C8,FIND("(",C8)+1,30))-1))[/SIZE][/TD]
May-17
[/td]$10,424,966.01 (4077)
[/td]10,424,966.01
[/td]4,077
[/td]Jun-17
[/td]$11,518,944.31 (2441)
[/td]11,518,944.31
[/td]2,441
[/td]Jul-17
[/td]$13,979,761.57 (5886)
[/td]13,979,761.57
[/td]5,886
[/td]Sum of Assigned Amounts prior to Sep17
[/td]Aug-17
[/td]$14,471,740.49 (4816)
[/td]14,471,740.49
[/td]4,816
[/td]50,395,412.38 =SUMIFS(D:D,B:B,"<"&B6)
[/td]Sep-17
[/td]$13,162,785.18 (4866)
[/td]13,162,785.18
[/td]4,866
[/td]'- need a formula to do this without using the helper columns
[/td]Oct-17
[/td]$13,564,501.05 (4219)
[/td]13,564,501.05
[/td]4,219
[/td]Nov-17
[/td]$14,502,575.36 (5642)
[/td]14,502,575.36
[/td]5,642
[/td]Dec-17
[/td]$16,383,880.72 (5586)
[/td]16,383,880.72
[/td]5,586
[/td]Jan-18
[/td]$21,483,088.34 (6129)
[/td]21,483,088.34
[/td]6,129
[/td]Feb-18
[/td]$16,041,918.66 (4454)
[/td]16,041,918.66
[/td]4,454
[/td]Mar-18
[/td]$27,936,497.70 (6845)
[/td]27,936,497.70
[/td]6,845
[/td]Apr-18
[/td]$3,776,844.67 (1033)
[/td]3,776,844.67
[/td]1,033
[/td]$177,247,504.06 (55994)
[/td]177,247,504.06
[/td]55,994
[/td][SIZE=11px]=??? need formulas to sum amount & count[/SIZE]
[/td]
[/TABLE]