# How sum column data inside and outside brackets in same cell

• 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"]

[tr]

[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]

[/tr]

[tr]

[td]

May-17

[/td]

[td]

\$10,424,966.01 (4077)

[/td]

[td]

10,424,966.01

[/td]

[td]

4,077

[/td]

[td][/td]

[/tr]

[tr]

[td]

Jun-17

[/td]

[td]

\$11,518,944.31 (2441)

[/td]

[td]

11,518,944.31

[/td]

[td]

2,441

[/td]

[td][/td]

[/tr]

[tr]

[td]

Jul-17

[/td]

[td]

\$13,979,761.57 (5886)

[/td]

[td]

13,979,761.57

[/td]

[td]

5,886

[/td]

[td]

Sum of Assigned Amounts prior to Sep17

[/td]

[/tr]

[tr]

[td]

Aug-17

[/td]

[td]

\$14,471,740.49 (4816)

[/td]

[td]

14,471,740.49

[/td]

[td]

4,816

[/td]

[td]

50,395,412.38 =SUMIFS(D:D,B:B,"<"&B6)

[/td]

[/tr]

[tr]

[td]

Sep-17

[/td]

[td]

\$13,162,785.18 (4866)

[/td]

[td]

13,162,785.18

[/td]

[td]

4,866

[/td]

[td]

'- need a formula to do this without using the helper columns

[/td]

[/tr]

[tr]

[td]

Oct-17

[/td]

[td]

\$13,564,501.05 (4219)

[/td]

[td]

13,564,501.05

[/td]

[td]

4,219

[/td]

[td][/td]

[/tr]

[tr]

[td]

Nov-17

[/td]

[td]

\$14,502,575.36 (5642)

[/td]

[td]

14,502,575.36

[/td]

[td]

5,642

[/td]

[td][/td]

[/tr]

[tr]

[td]

Dec-17

[/td]

[td]

\$16,383,880.72 (5586)

[/td]

[td]

16,383,880.72

[/td]

[td]

5,586

[/td]

[td][/td]

[/tr]

[tr]

[td]

Jan-18

[/td]

[td]

\$21,483,088.34 (6129)

[/td]

[td]

21,483,088.34

[/td]

[td]

6,129

[/td]

[td][/td]

[/tr]

[tr]

[td]

Feb-18

[/td]

[td]

\$16,041,918.66 (4454)

[/td]

[td]

16,041,918.66

[/td]

[td]

4,454

[/td]

[td][/td]

[/tr]

[tr]

[td]

Mar-18

[/td]

[td]

\$27,936,497.70 (6845)

[/td]

[td]

27,936,497.70

[/td]

[td]

6,845

[/td]

[td][/td]

[/tr]

[tr]

[td]

Apr-18

[/td]

[td]

\$3,776,844.67 (1033)

[/td]

[td]

3,776,844.67

[/td]

[td]

1,033

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

\$177,247,504.06 (55994)

[/td]

[td]

177,247,504.06

[/td]

[td]

55,994

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

[SIZE=11px]=??? need formulas to sum amount & count[/SIZE]

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[/TABLE]

## Files

• HI,

Use this formula: =SUMPRODUCT(--MID(C2:C13,2,FIND(" ",C2:C13)-1)*(B2:B13<B6))

You can use a separate cell instead of B6, and there put the date you want.

• That is outstanding! Thank you so much!!!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!