subtotal text groupings that change daily

JJI

New Member
Joined
Apr 21, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am a mid-level Excel user and slightly-more-than-novice VBA user. I'd like to be able to subtotal values within a text grouping, with the result displayed next to the last line in the text grouping. I tried SUMIF, but it returned subtotal for ALL matching text in my TYPE column. I want to subtotal EACH INSTANCE of matching text in my TYPE column. Then I'd like to subtotal those values within a different text group in my ACCOUNT column. Number of lines & types in each account can change daily. Is there Excel formula or VBA code to do this? Example of desired result attached.
Fidelity Daily Totals TEST.xlsx
BCDEFGH
1ACCOUNTSYMBOLVALUETYPESUBTOT TYPE% TYPESUBTOT ACCT
2XXXSRLN4585.00BL/Pref
3XXXVTA12661.00BL/Pref17246.000.09
4XXXBSCM21640.00Bond
5XXXBSJL13815.00Bond
6XXXBSJM46580.00Bond
7XXXBSJN2530.00Bond84565.000.43
8XXXSPAXX (Core account)17923.32Cash17923.320.09
9XXXGSY50470.00ST Bond
10XXXJPST25370.00ST Bond75840.000.38
11XXXSPHD871.00Stock
12XXXXLE1976.94Stock2847.940.01198422.26
13ZZZSRLN9170.00BL/Pref
14ZZZVTA1151.00BL/Pref
15ZZZVTA10359.00BL/Pref
16ZZZVVR2105.00BL/Pref
17ZZZVVR4210.00BL/Pref26995.000.12
18ZZZBSCL6336.00Bond
19ZZZBSCM21640.00Bond
20ZZZBSJL23025.00Bond
21ZZZBSJM5822.50Bond
22ZZZBSJM40757.50Bond
23ZZZBSJN2530.00Bond100111.000.43
24ZZZSPAXX (Core account)16076.75Cash16076.750.07
25ZZZKBWY567.25Reit567.250.00
26ZZZGSY50470.00ST Bond
27ZZZJPST30444.00ST Bond80914.000.35
28ZZZSPHD3266.25Stock
29ZZZXLE235.35Stock
30ZZZXLE2118.15Stock
31ZZZXLP699.20Stock6318.950.03230982.95
Sheet1
Cell Formulas
RangeFormula
F3,F27,F12,F10F3=SUM(D2:D3)
G3,G12,G10,G7:G8G3=F3/$H$12
F7,F31F7=SUM(D4:D7)
F8,F24:F25F8=SUM(D8)
H12H12=SUM(F2:F12)
F17F17=SUM(D13:D17)
G17,G31,G27,G23:G25G17=F17/$H$31
F23F23=SUM(D18:D23)
H31H31=SUM(F13:F31)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,081
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGH
1ACCOUNTSYMBOLVALUETYPESUBTOT TYPE% TYPESUBTOT ACCT
2XXXSRLN4585BL/Pref   
3XXXVTA12661BL/Pref17246.000.09 
4XXXBSCM21640Bond   
5XXXBSJL13815Bond   
6XXXBSJM46580Bond   
7XXXBSJN2530Bond84565.000.43 
8XXXSPAXX (Core account)17923.32Cash17923.320.09 
9XXXGSY50470ST Bond   
10XXXJPST25370ST Bond75840.000.38 
11XXXSPHD871Stock   
12XXXXLE1976.94Stock2847.940.01198422.26
13ZZZSRLN9170BL/Pref   
14ZZZVTA1151BL/Pref   
15ZZZVTA10359BL/Pref   
16ZZZVVR2105BL/Pref   
17ZZZVVR4210BL/Pref26995.000.12 
18ZZZBSCL6336Bond   
19ZZZBSCM21640Bond   
20ZZZBSJL23025Bond   
21ZZZBSJM5822.5Bond   
22ZZZBSJM40757.5Bond   
23ZZZBSJN2530Bond100111.000.43 
24ZZZSPAXX (Core account)16076.75Cash16076.750.07 
25ZZZKBWY567.25Reit567.250.00 
26ZZZGSY50470ST Bond   
27ZZZJPST30444ST Bond80914.000.35 
28ZZZSPHD3266.25Stock   
29ZZZXLE235.35Stock   
30ZZZXLE2118.15Stock   
31ZZZXLP699.2Stock6318.950.03230982.95
32
Staff
Cell Formulas
RangeFormula
F2:F31F2=IF(AND(B2=B3,E2=E3),"",SUMIFS(D:D,B:B,B2,E:E,E2))
G2:G31G2=IF(F2="","",F2/SUMIFS(D:D,B:B,B2))
H2:H31H2=IF(B2=B3,"",SUMIFS(D:D,B:B,B2))
 
  • Like
Reactions: JJI

JJI

New Member
Joined
Apr 21, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Yay Fluff! Exactly what was needed and works perfectly. Many thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,081
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,640
Messages
5,676,947
Members
419,660
Latest member
Fred Cailloux

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