# SUM Function or Formula or Working Solution for this situation needed please!

#### MikeRob

##### New Member
I'm having a Sum issue that I hope someone may be able to solve. I have a sample chart provided to help figure out. What I'm trying to achieve is the following:

Column A: Names of areas by ward and district
Column B: Number of attends total from that Area
Column C: Number of Attendants on Side 1 from that area
Column D: Number of Attendants on Side 2 from that area

there are a few of the areas that have only 1 district so those totals will remain the same. the goal is to construct a formula that accomplishes having areas with multiple districts provide the sums of B,C, and D in Columns E,F, and G of the last row of each area.

examples:

Aspinwall falls in Range A4:A6 so I need formula that will put the sum of B4:B6 in Cell E6, the sum of C4:C6 in Cell F6, and the sum of D4:D6 in Cell G6

Avalon falls in Range A7:A12 so I need formula that will put the sum of B7:B12 in Cell E12, the sum of C7:C12 in Cell F12, and the sum of D7:D12 in Cell G12

No I was doing this manually by summing these ranges with function insert and highlighting each range, but figured there must be a more efficient way to do this simultaneously with a formula or function or combination of either or. I cant quite figure how to do this of if I'm trying to accomplish something that cant be done so I'm seeking the help of this beloved community. Thank you in advanced to all who can, will, or try to help.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

##### Well-known Member
Hi MikeRob,

This relies on the first word of the ward is the identifier by which you want to sub-total.

MikeRob.xlsx
ABCDEFG
3Aleppo1112211122
4Aspinwall Ward 121233
5Aspinwall Ward 231344
6Aspinwall Ward 341455939132
7Avalon Ward 151566
8Avalon Ward 2 Dist 161677
9Avalon Ward 2 Dist 271788
10Avalon Ward 2 Dist 3818992666330
11
12
Sheet1
Cell Formulas
RangeFormula
E3:G12E3=IF(COUNTIF(\$A3:\$A\$9999,LEFT(\$A3,IFERROR(SEARCH(" ",\$A3),LEN(\$A3)))&"*")<>1,"",SUMIF(\$A\$3:\$A\$9999,LEFT(\$A3,IFERROR(SEARCH(" ",\$A3),LEN(\$A3)))&"*",B\$3:B\$9999))

#### MikeRob

##### New Member
O M G that is Spot On!!! Thank you ever so much!!! its ironic that in in the middle of just learning how to combine functions, and I was going through every one of these presented to figure this out, but I didn't think of the SEARCH and LEN functions. Again you've been a great help. Will make sure to follow you here for any more help!!

##### Well-known Member
You're welcome. Glad I could help.

#### Peter_SSs

##### MrExcel MVP, Moderator
If your column A is grouped by Area as in your example, this should also suffice.
Note though that this suggestion (& Toadstool's) could return incorrect results if Area names can consist of more than one word. If that is possible, post back for a (longer) alternative.

20 07 25.xlsm
ABCDEFG
1
2
3Aleppo1112211122
4Aspinwall Dist 121233
5Aspinwall Dist 231344
6Aspinwall Dist 341455939132
7Avalon Ward 151566
8Avalon Ward 2 Dist 161677
9Avalon Ward 2 Dist 271788
10Avalon South Ward 1818992666330
Sums
Cell Formulas
RangeFormula
E3:G10E3=IF(LEFT(\$A3,FIND(" ",\$A3&" "))=LEFT(\$A4,FIND(" ",\$A4&" ")),"",SUM(B\$3:B3)-SUM(E\$2:E2))

Replies
4
Views
87
Replies
7
Views
94
Replies
0
Views
76
Replies
4
Views
164
Replies
3
Views
57