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

MikeRob

New Member
Joined
May 8, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

1595630935846.png


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.
 

Some videos you may like

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.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
999
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 8, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
999
Office Version
  1. 2016
Platform
  1. Windows
You're welcome. Glad I could help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,891
Office Version
  1. 365
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,255
Messages
5,546,801
Members
410,759
Latest member
Bufnercash
Top