extracting data with sorted and subtotal

E

earth

New Member
Joined
Nov 24, 2024
Messages
2
Office Version
  1. Prefer Not To Say
Platform
  1. Windows
Dear,
First of all thanks for creating such a helpful site to guide us.

I am creating my Mutual Fund Porfolio
I have mixed data as i have input data of all my family members in one page
I would like to extract data with sorting and doing Subtotal and Total for it
attaching my sheet
Sheet1 is mixed data table
Sheet A shows extracted values of Name: "A"
Extraced sorting/filtering is like
A has Scheme Name : "Mutual Fund" which might have 1 or 2 folios in it.
So extracting folios for Scheme Name for Family member A in Sheet A and doing subtotal as per folio.
same thing with Member B, C, D,E
Attaching my example file
Please guide,
Thanks


Sheet1

temp.xlsx
ABCDEFGHIJKLMNOPQRSTU
2AParag Parikh Flexi Cap Fund - Direct Plan - Growth13124213-11-2024SIP5,00058.414085.592377,690 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!0.01#REF!
3DParag Parikh Flexi Cap Fund - Direct Plan - Growth13122813-11-2024SIP5,00058.414085.592377,690 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!0.01#REF!
4CCanara Robeco Manufacturing Fund - Direct Plan - Growth Option19939692605-11-2024SIP1,500120.476012.450079,476 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!2.25#REF!
5Bquant ELSS Tax Saver Fund - Growth Option - Direct Plan51043561506-11-2024SIP1,0002.3280429.536680,378 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!3.35#REF!
6CParag Parikh Flexi Cap Fund - Direct Plan - Growth14461507-11-2024SIP2,50028.777086.870579,541 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!2.33#REF!
7AAxis Small Cap Fund - Direct Plan - Growth910163172504-11-2024SIP1,0008.3230120.140078,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
8AHDFC Defence Fund - Growth Option - Direct Plan20554404-11-2024SIP1,50071.340021.025078,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
9AHDFC Small Cap Fund - Growth Option - Direct Plan20554404-11-2024SIP1,0006.3310157.951078,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
10ANippon India Small Cap Fund - Direct Plan Growth Plan - Growth Option49931474704-11-2024SIP1,5007.6640195.699878,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
11AParag Parikh Flexi Cap Fund - Direct Plan - Growth13124204-11-2024SIP1,50017.489085.764578,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
12AQuant Small Cap Fund - Growth Option - Direct Plan510196304-11-2024SIP1,5005.1600290.698978,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
13ASBI Small Cap Fund - Direct Plan - Growth29141304-11-2024SIP1,0004.8970204.182178,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
14DDSP Small Cap Fund - Direct Plan - Growth36160404-11-2024SIP2,0009.2980215.092078,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
15DParag Parikh Flexi Cap Fund - Direct Plan - Growth13104-11-2024SIP1,50017.489085.764578,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
16DQuant Small Cap Fund - Growth - Regular Plan510197704-11-2024SIP1,5005.5690269.348378,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
17ENippon India Small Cap Fund - Direct Plan Growth Plan - Growth Option4993200304-11-2024SIP5,00025.5480195.699878,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
18EParag Parikh Flexi Cap Fund - Direct Plan - Growth145204-11-2024SIP2,50029.148085.764578,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
19FBandhan Small Cap Fund - Direct Plan Growth4845904-11-2024SIP1,00019.909050.225078,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
20FQuant Small Cap Fund - Growth Option - Direct Plan510188904-11-2024SIP2,0006.8800290.698978,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
21FSBI Small Cap Fund - Direct Plan - Income Distribution cum Capital Withdrawal Option (IDCW)26043904-11-2024SIP2,00014.3500139.362978,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
22BKotak Emerging Equity Scheme - Growth - Direct10382804-11-2024SIP1,0006.7140148.941078,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
23BNippon India Small Cap Fund - Direct Plan Growth Plan - Growth Option40133704-11-2024SIP1,0005.1100195.699878,782 #VALUE!#REF!#REF!#REF!#VALUE!#VALUE!1.39#REF!
Sheet1
Cell Formulas
RangeFormula
O2:O23O2=IF(F2>0,'M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$B$2-'M:\AmbaShare\MF\MF all 20241114.xlsx'!CombEntry[@[Tran Date]],"")
P2:P23P2=IF(O2="","",IF(O2<365,(M2-F2),""))
Q2:Q23Q2=IF(O2="","",IF(O2>365,(M2-F2),""))
R2:R23R2=IF(D2>0,(100*(M2-F2)/F2),"")
S2:S23S2=IF(D2>0,(((M2/F2)^(365/O2)-1)*100),"")
T2:T23T2=IF(D2>0,(100*('M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$B$4-J2)/J2),"")
U2:U23U2=IF(D2>0,(100*(('M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$B$4/J2)^(365/O2)-1)),"")
K2:K23K2=IFERROR(VLOOKUP(B2,'M:\AmbaShare\MF\MF all 20241114.xlsx'!NAVAll[[Scheme Name]:[Date]],4,FALSE),"")
M2:M23M2=IF(D2>0,H2*K2,"")


Sheet A

temp.xlsx
ABCDEFGHIJKLMNOPQR
1A
2Tran DateTran TypePur AmtDiv ReinPur NAVUnitsSensexCurr NAV31st Jan-18Curr AmtDiv AmtDaysGL STGL LTRet ABSRet CAGRIndex ABSIndex CAGR
3
4
5
6Aditya Birla Sun Life Frontline Equity Fund - Growth10166NAV495.7000XIRR12.85%Date########
722-10-2014PUR50000.00146.740034.074061511495.7000NA16,890.480.003691 11890.48237.8112.7926.292.34
8         
9         
1014-11-2024-16890
11Total50000.00146.740034.074061,511495.700016,890.480.000.0011,890.48237.8112.8526.292.34
12
13
14Axis Small Cap Fund - Direct Plan - Growth91016NAV116.3800XIRR#VALUE!Date########
1502-01-2023SIP10000.0072.010013.886061168116.3800NA1,616.050.00697 616.0561.6128.5827.0013.33
1601-02-2023SIP10000.0070.840014.116059708116.3800NA1,642.820.00667 642.8264.2831.2130.1115.49
1701-03-2023SIP10000.0070.490014.186059411116.3800NA1,650.970.00639 650.9765.1033.1630.7616.55
1803-04-2023SIP10000.0069.830014.320059106116.3800NA1,666.560.00606 666.5666.6636.0231.4317.90
1902-05-2023SIP10000.0072.700013.754061355116.3800NA1,600.690.00577 600.6960.0734.6626.6116.10
2001-06-2023SIP10000.0077.450012.911062429116.3800NA1,502.580.00547 502.5850.2631.2224.4415.71
21Total10000.0070.490014.186059,411116.38001,650.970.000.00650.9765.10#NUM!30.7616.55
22
23Grand Total6,0000.0073.120648.260064,57218,541.450.000.0012,541.45
24OverAll Weg.CAGR : 44.63 OverAll Ret. ABS : 28.13Total GainLoss : 25,318.58
25
A
Cell Formulas
RangeFormula
P6P6=XIRR(C7:C10,A7:A10)
R6,R14R6='M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$B$3
L7:L9,L15:L20L7=IF(C7>0,'M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$B$2-'M:\AmbaShare\MF\[MF all 20241114.xlsx]Aditi'!A7,"")
M7:M9,M15:M20M7=IF(L7="","",IF(L7<365,(J7-C7),""))
N7:N9,N15:N20N7=IF(L7="","",IF(L7>365,(J7-C7),""))
O7:O9,O15:O20O7=IF(A7>0,(100*(J7-C7)/C7),"")
P7:P9,P15:P20P7=IF(A7>0,(((J7/C7)^(365/L7)-1)*100),"")
Q7:Q9,Q15:Q20Q7=IF(A7>0,(100*('M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$B$4-G7)/G7),"")
R7:R9,R15:R20R7=IF(A7>0,(100*(('M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$B$4/G7)^(365/L7)-1)),"")
H7:H9H7=IF(A7>0,$J$6,"")
J6J6='M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$E$11
J7:J9,J15:J20J7=IF(A7>0,F7*H7,"")
A10A10=R6
C10C10=-($J$11)
C11,J21,F21,C21,M21:N21,M11:N11,J11,F11C11=SUM(C7)
E11,G11:H11,E21,G21:H21,O21,Q21:R21,O11,Q11:R11E11=AVERAGE(E7)
P11,P21P11=XIRR(C7:C10,A7:A10)*100
P14P14=XIRR(C15:C38,A15:A38)
H15:H20H15=IF(A15>0,$J$14,"")
J14J14='M:\AmbaShare\MF\[MF all 20241114.xlsx]Schemes'!$E$12
C23,M23:N23,J23,F23C23=C11+C21
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

B
  • Question Question
How to make this IF formula work?
Replies
1
Views
249
Fluff
Fluff
D
  • Question Question
Having trouble getting Pivot Table data to pull from pivot table to regular table using =GetPivotData()
Replies
4
Views
1K
smozgur
smozgur
D
  • Solved
New formula for cumulative values
Replies
6
Views
176
djclements
djclements
L
  • Solved
Updating data in cell, run the macro again and it should not happen.
2 3 4
Replies
34
Views
1K
mumps
mumps
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
256
Joe4
Joe4

Forum statistics

Threads
1,224,504
Messages
6,179,142
Members
452,892
Latest member
JUSTOUTOFMYREACH

Share this page

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
Back
Top