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

[MikeRob]

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.

 

[Toadstool]

Hi MikeRob,

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

MikeRob.xlsx
	A	B	C	D	E	F	G
3	Aleppo	1	11	22	1	11	22
4	Aspinwall Ward 1	2	12	33
5	Aspinwall Ward 2	3	13	44
6	Aspinwall Ward 3	4	14	55	9	39	132
7	Avalon Ward 1	5	15	66
8	Avalon Ward 2 Dist 1	6	16	77
9	Avalon Ward 2 Dist 2	7	17	88
10	Avalon Ward 2 Dist 3	8	18	99	26	66	330
11
12
Sheet1

Cell Formulas
Range	Formula
E3:G12	E3	=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]

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!!

 

[Toadstool]

You're welcome. Glad I could help.

 

[Peter_SSs]

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
	A	B	C	D	E	F	G
1
2
3	Aleppo	1	11	22	1	11	22
4	Aspinwall Dist 1	2	12	33
5	Aspinwall Dist 2	3	13	44
6	Aspinwall Dist 3	4	14	55	9	39	132
7	Avalon Ward 1	5	15	66
8	Avalon Ward 2 Dist 1	6	16	77
9	Avalon Ward 2 Dist 2	7	17	88
10	Avalon South Ward 1	8	18	99	26	66	330
Sums

Cell Formulas
Range	Formula
E3:G10	E3	=IF(LEFT($A3,FIND(" ",$A3&" "))=LEFT($A4,FIND(" ",$A4&" ")),"",SUM(B$3:B3)-SUM(E$2:E2))