sum table based on rows and multiple years columns

V

vampsthevampyre

New Member
Joined
Apr 29, 2016
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Morning All,

Hopefully final one for my dividends spreadsheet. As dividends are generated they are added to table B7. These are then used to generate a dynamic array V4# (no zeros - personal preference to display even though it screws me over later on due to text/number mixed arrays).

What I'm looking to generate is a dynamic array similar to V67#-X67# which would be compiled from the above table and would include total dividends paid for each year per stock. Each stock could potentially have twelve dividends paid each month for a year. I'm looking to be able to monitor how much the dividends pay each year and how the grow.

as usual any help will be appreciated as sumifs isn't doing it for me

regards

Ian

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
12023202320232023202420242025
2Monthly Average108.39Mar-23Apr-23May-23Jun-23452924544445658
3Total758.728.77132.73441.01627.35704.86730.83758.72
4Expected25.50275.83543.19286.12103.98286.12103.98
5
6DividendsStock Div8.77123.96308.28186.3477.5125.9727.89
7NameTickerDateDividendDayMonthYearShare EqvMar 20232023627.35NameTickerTotalsMar 2023Apr 2023May 2023Jun 2023Jan 2024Jun 2024Jan 2025
8Unilever PLCULVR21/03/20238.7721Mar2023-Apr 20232024103.48MONY111.93British American Tobacco PLCBATS105.8 25.9725.9725.9727.89
9Diageo PLCDGE13/04/20231.5413Apr2023-May 2023202527.89BATS105.8B&M European Value Retail SABME0
10Rio Tinto PLCRIO20/04/202359.3120Apr20231Jun 2023CSN103.08Castings PLCCGS0
11M&G PlcMNG27/04/202363.1127Apr202332Jan 2024GLEN66.68ChesnaraCSN103.0851.5451.54
12British American Tobacco PLCBATS03/05/202325.973May20231Jun 2024MNG63.11DCC plcDCC0
13Persimmon PLCPSN05/05/202339.65May20233Jan 2025RIO59.31Diageo PLCDGE1.541.54
14Phoenix Group Holdings PLCPHNX10/05/202339.7810May20237LGEN52.66Glencore PlcGLEN66.6866.68
15Moneysupermarket.Com Group PLCMONY11/05/2023111.9311May202341VTY41.28GSK plcGSK0
16Primary Health PropertiesPHP19/05/202322.619May202323PHNX39.78Legal & General Group PLCLGEN52.6652.66
17ChesnaraCSN26/05/202351.5426May202318PSN39.6LondonMetric Property PLCLMP0
18Target Healthcare REIT LTDTHRL26/05/202316.8626May202323ULVR34.49Morgan Sindall Group PLCMGNS0
19Vistry Group PLCVTY01/06/202341.281Jun20235PHP22.6Moneysupermarket.Com Group PLCMONY111.93111.93
20Glencore PlcGLEN02/06/202366.682Jun202314THRL16.86M&G PlcMNG63.1163.11
21Legal & General Group PLCLGEN05/06/202352.665Jun202323DGE1.54National Grid PLCNG.0
22Unilever PLCULVR15/06/202325.7215Jun2023-BME0Phoenix Group Holdings PLCPHNX39.7839.78
23ChesnaraCSN01/01/202451.541Jan202418CGS0Primary Health PropertiesPHP22.622.60
24British American Tobacco PLCBATS01/01/202425.971Jan20241DCC0Persimmon PLCPSN39.639.60
25British American Tobacco PLCBATS01/06/202425.971Jun20241GSK0Rio Tinto PLCRIO59.3159.31
26British American Tobacco PLCBATS01/01/202527.891Jan20251LMP0Sirius Real Estate LTDSRE0
27MGNS0Strix Group PLCKETL0
28NG.0Tandem Group PLCTND0
29SRE0Target Healthcare REIT LTDTHRL16.8616.86
30KETL0Unilever PLCULVR34.498.7725.72
31TND0United Utilities Group PLCUU.0
32UU.0Vodafone Group PLCVOD0
33VOD0Vistry Group PLCVTY41.2841.28
34
35
36
37Year2023202320232023202420242025
38British American Tobacco PLCBATS0025.97025.9725.9727.89
39B&M European Value Retail SABME0000000
40Castings PLCCGS0000000
41ChesnaraCSN0051.54051.5400
42DCC plcDCC0000000
43Diageo PLCDGE01.5400000
44Glencore PlcGLEN00066.68000
45GSK plcGSK0000000
46Legal & General Group PLCLGEN00052.66000
47LondonMetric Property PLCLMP0000000
48Morgan Sindall Group PLCMGNS0000000
49Moneysupermarket.Com Group PLCMONY00111.930000
50M&G PlcMNG063.1100000
51National Grid PLCNG.0000000
52Phoenix Group Holdings PLCPHNX0039.780000
53Primary Health PropertiesPHP0022.60000
54Persimmon PLCPSN0039.60000
55Rio Tinto PLCRIO059.3100000
56Sirius Real Estate LTDSRE0000000
57Strix Group PLCKETL0000000
58Tandem Group PLCTND0000000
59Target Healthcare REIT LTDTHRL0016.860000
60Unilever PLCULVR8.770025.72000
61United Utilities Group PLCUU.0000000
62Vodafone Group PLCVOD0000000
63Vistry Group PLCVTY00041.28000
64
65
66Year202320242025
67British American Tobacco PLCBATS25.9751.9427.89
68B&M European Value Retail SABME000
69Castings PLCCGS000
70ChesnaraCSN51.5451.540
71DCC plcDCC000
72Diageo PLCDGE1.5400
73Glencore PlcGLEN66.6800
74GSK plcGSK000
75Legal & General Group PLCLGEN52.6600
76LondonMetric Property PLCLMP000
77Morgan Sindall Group PLCMGNS000
78Moneysupermarket.Com Group PLCMONY111.9300
79M&G PlcMNG63.1100
80National Grid PLCNG.000
81Phoenix Group Holdings PLCPHNX39.7800
82Primary Health PropertiesPHP22.600
83Persimmon PLCPSN39.600
84Rio Tinto PLCRIO59.3100
85Sirius Real Estate LTDSRE000
86Strix Group PLCKETL000
87Tandem Group PLCTND000
88Target Healthcare REIT LTDTHRL16.8600
89Unilever PLCULVR34.4900
90United Utilities Group PLCUU.000
91Vodafone Group PLCVOD000
92Vistry Group PLCVTY41.2800
93
Dividends Paid
Cell Formulas
RangeFormula
S2S2=AVERAGE(V6#)
S3S3=SUM(V6#)
V1:AB1V1=YEAR(V2#)
V2:AB2V2=DATEVALUE(V7#)
V3:AB3V3=SCAN(0,V6#,LAMBDA(a,v,a+v))
V4:AB4V4=XLOOKUP(MONTH(V2#),MONTH(DATEVALUE("1-"&'[Stock and Shares (1).xlsx]New Expected Dividends'!I7#&"-2000")),'[Stock and Shares (1).xlsx]New Expected Dividends'!I5#,"Missing",0)
K7:K13K7=UNIQUE(TblDividendsPaid[Month]&" "&TblDividendsPaid[Year])
L7:L9L7=UNIQUE(TblDividendsPaid[Year])
M7:M9M7=SUMIFS(TblDividendsPaid[Dividend],TblDividendsPaid[Year],L7#)
F8:F26F8=TEXT([@Date],"D")
G8:G26G8=TEXT([@Date],"MMM")
H8:H26H8=YEAR([@Date])
I8:I26I8=ROUNDDOWN([@Dividend]/(INDEX('Stock and Shares (1).xlsx'!DividendDatesTable[Current Share Price],MATCH([@Ticker],'Stock and Shares (1).xlsx'!DividendDatesTable[Symbol],0))/100),0)
O8:P33O8=SORTBY(S8#:T8#,T8#,-1)
R8:R33,R67:R92,R38:R63R8=FILTER('Stock and Shares (1).xlsx'!DividendDatesTable[Name],'Stock and Shares (1).xlsx'!DividendDatesTable[Current Shares]>0)
S8:S33,S67:S92,S38:S63S8=XLOOKUP(R8#,'Stock and Shares (1).xlsx'!DividendDatesTable[Name],'Stock and Shares (1).xlsx'!DividendDatesTable[Symbol],"Missing",0)
T8:T33T8=SUMIFS(TblDividendsPaid[Dividend],TblDividendsPaid[Ticker],S8#)
V6:AB6V6=BYCOL(V8#,LAMBDA(y,SUM(y)))
V7:AB7V7=TRANSPOSE(K7#)
V8:AB33V8=XLOOKUP(S8#&" "&V7#,TblDividendsPaid[Ticker]&" "&TblDividendsPaid[Month]&" "&TblDividendsPaid[Year],TblDividendsPaid[Dividend],"",0)
C8:C26C8=XLOOKUP([@Name],'Stock and Shares (1).xlsx'!DividendDatesTable[Name],'Stock and Shares (1).xlsx'!DividendDatesTable[Symbol],"",0)
V37:AB37V37=YEAR(V2#)
V38:AB63V38=IF(V8#="",0,V8#)
V66:X66V66=TRANSPOSE(UNIQUE(TblDividendsPaid[Year]))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
DividendsPaidAmount='Dividends Paid'!$V$6#V3, S2:S3
DividendsPaidMonthYear='Dividends Paid'!$V$2#V37, V4, V1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
X68:X92Expression=ISNUMBER($T68)textNO
W68:W92Expression=ISNUMBER($T68)textNO
V7:AH7Expression=AND(V$7<>"",W$7="")textNO
V7:AH7Expression=V$7<>""textNO
V1:V4,AA5,V6:V1048576Expression=ISNUMBER($T1)textNO
V1:AH2,W5:Z5,AB5:AH5,V6:AH1048576Expression=AND(ISNUMBER($T1),V$7<>"",W$7="")textNO
V1:AH2,W5:Z5,AB5:AH5,V6:AH1048576Expression=AND($T1<>"",$T2="",V$7<>"")textNO
V3:AH4Expression=AND(ISNUMBER($T3),V$7<>"",W$7="")textNO
V3:AH4Expression=AND($T3<>"",$T5="",V$7<>"")textNO
AA5Expression=AND(ISNUMBER($T5),V$7<>"",W$7="")textNO
AA5Expression=AND($T5<>"",$T6="",V$7<>"")textNO
Cells with Data Validation
CellAllowCriteria
B8:B35List=LstDividendName
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Sumifs or a Pivot Table would yield a summary by dividend, name, and year.

What is the issue with Sumifs or Pivot Table?
 
Upvote 0
Solution
The issue is that I'm over thinking it, sumif worked perfectly sorry for wasting everybody's time
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
347
Greenbehindthecells
G
D
  • Question
Organizing data for a textbook order workbook
Replies
0
Views
162
djaida
D
willow1985
  • Solved
Assistance with VBA error handling - a cell not matching data set
2
Replies
10
Views
960
willow1985
willow1985
S
  • Solved
get total by row across multiple columns, each column with a different amount
Replies
3
Views
411
Fluff
Fluff
J
  • Question
Populate columns based on multiple criteria
Replies
0
Views
410
JonasTiger
J

Forum statistics

Threads
1,215,069
Messages
6,122,952
Members
449,095
Latest member
nmaske

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