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

MikeRob

New Member
Joined
May 8, 2020
Messages
27
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
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))
 
Solution

MikeRob

New Member
Joined
May 8, 2020
Messages
27
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
2,549
Office Version
  1. 2016
Platform
  1. Windows
You're welcome. Glad I could help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,365
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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,115
Messages
5,835,471
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top