calculating stock market bonus shares

[mwvirk]

hello

I am calculating how many bonus shares (Flag marked as B) I have received from the stock market for each of my stocks
and then also checking if I sell some bonus shares then how many are still in my portfolio

in this excel file, I have 2 sheets
sheet1 is for buying, selling and bonus shares received
sheet2 is to check the status that how many bonus shares were received and how many are left. if all are sold then I should get 0 in sheet2 cell D
(but in all cases, I want to keep the actual bonus shares received in sheet2 cell C)

I have multiple issues here in this sheet2:

1- if the cell H in sheet1 is blank then cell D in sheet2 is not counting the total (i should have a value of 0 at least in sheet1 cell H to get the result since the blank cell is not showing the count in sheet2 cell D)
2- if I partially or fully sell bonus shares in sheet1 then it's showing balance zero in all cases in sheet2 cell D (i am checking here to know how many are still in my portfolio)

kindly check and help.

thanks

Test-Copy.xlsx
	A	B	C	D	E	F	G	H
1	Stock name		Flag			Received		Sold
2	Apple		R			100		0
3	Google		R			150		0
4	Intel		R			200		0
5	Facebook		R			250		0
6	Apple		B			10		5
7	Google		B			15		0
8	Intel		B			20		0
9	Facebook		B			25		0
10	Apple		R			200		0
11	Google		R			250		0
12	Intel		R			300		0
13	Facebook		R			350		0
14	Apple		B			15
15	Google		B			20
16	Intel		B			25
17	Facebook		B			30
Sheet1

Test-Copy.xlsx
	A	B	C	D
1	Apple		25	0
2	Google		35	15
3	Intel		45	20
4	Facebook		55	25
Sheet2

Cell Formulas
Range	Formula
C1:C4	C1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B")
D1:D4	D1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B",Sheet1!H:H,0)

 

[KRice]

Try this:

Book4
	A	B	C	D
1	Apple		25	20
2	Google		35	35
3	Intel		45	45
4	Facebook		55	55
Sheet2

Cell Formulas
Range	Formula
C1:C4	C1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B")
D1:D4	D1	=C1-SUMIFS(Sheet1!H:H,Sheet1!A:A,A1,Sheet1!C:C,"B")

 

[mwvirk]

Try this:

Book4
	A	B	C	D
1	Apple		25	20
2	Google		35	35
3	Intel		45	45
4	Facebook		55	55
Sheet2

Cell Formulas
Range	Formula
C1:C4	C1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B")
D1:D4	D1	=C1-SUMIFS(Sheet1!H:H,Sheet1!A:A,A1,Sheet1!C:C,"B")

thanks, KRice
your formula is working fine on the test sheet but when I am applying it to my actual worksheet, it's getting all the shares in hand even if those are not bonus shares with flag "B"
it seems I have to check the actual sheet why your formula is not working there.

 

[KRice]

Check the column references to determine if the formula needs to be revised to match your actual worksheet. Also confirm that the Flag column contains "B" with no extra characters around it.

 

[mwvirk]

Check the column references to determine if the formula needs to be revised to match your actual worksheet. Also confirm that the Flag column contains "B" with no extra characters around it.

MWVirk PSX Portfolio.xlsx
	AT	AU	AV	AW
1	Number Of Times Bonus Shares Received	Total Bonus Shares Received	Total Bonus Shares In Inventory	Number Of Times Dividends Received
2	0	0	0	0
3	0	0	0	0
4	0	0	0	0
5	0	0	0	0
6	0	0	0	0
7	0	0	0	0
8	0	0	0	0
9	1	1,000	1,000	0
10	0	0	0	1
11	0	0	0	0
12	1	300	300	0
13	0	0	0	0
PSX Portfolio Status

Cell Formulas
Range	Formula
AT2:AT13	AT2	=COUNTIFS('PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")
AU2:AU13	AU2	=SUMIFS('PSX Trading'!F:F,'PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")
AV2:AV13	AV2	=SUMIFS('PSX Trading'!F:F,'PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B",'PSX Trading'!AD:AD,0)
AW2:AW13	AW2	=IF(A2<>"",COUNTIF('RDA HBL Statement'!$B$16:$B$1200,"*"&A2&" Dividend"&"*"),0)

Named Ranges
Name	Refers To	Cells
'PSX Trading'!_FilterDatabase	='PSX Trading'!$A$1:$BU$1201	AT2:AV13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AV3:AV112	Cell Value	<>0	text	NO
AV2	Cell Value	<>0	text	NO
AU95:AU112,AU2:AU80	Cell Value	<>0	text	NO
AT95:AT112,AT2:AT80	Cell Value	<>0	text	NO
AW2:AW18,AW95:AW112,AW20:AW80	Cell Value	<>0	text	NO

above is without using your formula...
and below is after using your formula...

MWVirk PSX Portfolio.xlsx
	AT	AU	AV
1	Number Of Times Bonus Shares Received	Total Bonus Shares Received	Total Bonus Shares In Inventory
2	0	0	0
3	0	0	0
4	0	0	12,500
5	0	0	0
6	0	0	0
7	0	0	0
8	0	0	0
9	1	1,000	25,000
10	0	0	0
11	0	0	0
12	1	300	7,000
PSX Portfolio Status

Cell Formulas
Range	Formula
AT2:AT12	AT2	=COUNTIFS('PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")
AU2:AU12	AU2	=SUMIFS('PSX Trading'!F:F,'PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")
AV2:AV12	AV2	=C2-SUMIFS('PSX Trading'!AD:AD,'PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")

Named Ranges
Name	Refers To	Cells
'PSX Trading'!_FilterDatabase	='PSX Trading'!$A$1:$BU$1201	AT2:AV12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AV3:AV12	Cell Value	<>0	text	NO
AV2	Cell Value	<>0	text	NO
AU95:AU112,AU2:AU80	Cell Value	<>0	text	NO
AT95:AT112,AT2:AT80	Cell Value	<>0	text	NO

 

[mwvirk]

i think i copied extra cells as dividend is also showing in 1st mini sheet (please ignore dividend cells)

 

[KRice]

Your post shows columns AT, AU, and AV. I can't see your 'PSX Trading' sheet, but based on the formulas shown in the 'PSX Portfolio Status' sheet...

1. I would expect to find your flag column (with some cells containing "B") in column C of 'PSX Trading'. Is this correct?
2. I would expect to find the stock names in column A of 'PSX Trading'. Is this correct?
3. I would expect to find the "Received" quantities in column F of 'PSX Trading'. Is this correct?

Then the formula shown in AU2:AU12 of 'PSX Portfolio Status'...

=SUMIFS('PSX Trading'!F:F,'PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")

...should return the total "B" bonus shares received for each stock name listed in A2 and down.

Since these are all 0, I am guessing that a column reference is not correct. Could you confirm my questions above?

Then for the bonus shares remaining, I am guessing that column AD of 'PSX Trading' shows the "Sold" bonus shares. Is this correct"
Then the bonus shares remaining in AV2:AV12 would be...

=AU2-SUMIFS('PSX Trading'!AD:AD,'PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")

...but check the column references, as there are clues in the first formula suggesting that at least one incorrect column is being referenced.

 

[mwvirk]

Your post shows columns AT, AU, and AV. I can't see your 'PSX Trading' sheet, but based on the formulas shown in the 'PSX Portfolio Status' sheet...

1. I would expect to find your flag column (with some cells containing "B") in column C of 'PSX Trading'. Is this correct?
2. I would expect to find the stock names in column A of 'PSX Trading'. Is this correct?
3. I would expect to find the "Received" quantities in column F of 'PSX Trading'. Is this correct?

Then the formula shown in AU2:AU12 of 'PSX Portfolio Status'...

=SUMIFS('PSX Trading'!F:F,'PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")

...should return the total "B" bonus shares received for each stock name listed in A2 and down.

Since these are all 0, I am guessing that a column reference is not correct. Could you confirm my questions above?

Then for the bonus shares remaining, I am guessing that column AD of 'PSX Trading' shows the "Sold" bonus shares. Is this correct"
Then the bonus shares remaining in AV2:AV12 would be...

=AU2-SUMIFS('PSX Trading'!AD:AD,'PSX Trading'!A:A,A2,'PSX Trading'!C:C,"B")

...but check the column references, as there are clues in the first formula suggesting that at least one incorrect column is being referenced.

amazing. unbelievable. man you have decoded the entire sheets even though I was trying to hide my privacy for not sharing the original sheets.
all your expectations are correct and the formula given at the end really worked perfectly.
superb. thanks a lot, dude.
I have 1 more sheet and I wish if you can help me with that. shall I post it here, please? thanks again.

 

[mwvirk]

Test-0000.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	NA # (2018)	Province	NA # (2013)	Seat Name	Registered Voters	Total Cast Votes	Rejected Votes	Valid Cast Votes	% Of Total Registered Votes	Candidate Name	Party Name	Votes	% Of Candidate Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party	Previous Winner's Name In 2018	Previous Party Name In 2018
2	NA-001	Province-1	Name-001	Area-001	318,150	48,272	5,000	43,272	15.17%	Candidate Name 0001	Party Name AA	1,000	2.31%	Candidate Name 0027	Party Name BA	Candidate Name 0004	Party Name AD			1122	1122	1122	1122	1122
3										Candidate Name 0002	Party Name AB	500	1.16%
4										Candidate Name 0003	Party Name AC	2	0.00%
5										Candidate Name 0004	Party Name AD	9,000	20.80%
6										Candidate Name 0005	Party Name AE	4	0.01%
7										Candidate Name 0006	Party Name AF	2,700	6.24%
8										Candidate Name 0007	Party Name AG	6	0.01%
9										Candidate Name 0008	Party Name AH	7	0.02%
10										Candidate Name 0009	Party Name AI	2,700	6.24%
11										Candidate Name 0010	Party Name AJ	10	0.02%
12										Candidate Name 0011	Party Name AK	11	0.03%
13										Candidate Name 0012	Party Name AL	12	0.03%
14										Candidate Name 0013	Party Name AM	13	0.03%
15										Candidate Name 0014	Party Name AN	14	0.03%
16										Candidate Name 0015	Party Name AO	15	0.03%
17										Candidate Name 0016	Party Name AP	16	0.04%
18										Candidate Name 0017	Party Name AQ	17	0.04%
19										Candidate Name 0018	Party Name AR	18	0.04%
20										Candidate Name 0019	Party Name AS	19	0.04%
21										Candidate Name 0020	Party Name AT	20	0.05%
22										Candidate Name 0021	Party Name AU	21	0.05%
23										Candidate Name 0022	Party Name AV	22	0.05%
24										Candidate Name 0023	Party Name AW	23	0.05%
25										Candidate Name 0024	Party Name AX	24	0.06%
26										Candidate Name 0025	Party Name AY	25	0.06%
27										Candidate Name 0026	Party Name AZ	26	0.06%
28										Candidate Name 0027	Party Name BA	27,000	62.40%
29										Candidate Name 0028	Party Name BB	28	0.06%
30										Candidate Name 0029	Party Name BC	9	0.02%
31										Candidate Name 0030	Party Name BD	10	0.02%
32	NA-002	Province-1	Name-002	Area-002						Candidate Name 0001	Party Name AA
33										Candidate Name 0002	Party Name AB
34										Candidate Name 0003	Party Name AC
35										Candidate Name 0004	Party Name AD
36										Candidate Name 0005	Party Name AE
37										Candidate Name 0006	Party Name AF
38										Candidate Name 0007	Party Name AG
39										Candidate Name 0008	Party Name AH
40										Candidate Name 0009	Party Name AI
41										Candidate Name 0010	Party Name AJ
42										Candidate Name 0011	Party Name AK
43										Candidate Name 0012	Party Name AL
44										Candidate Name 0013	Party Name AM
45										Candidate Name 0014	Party Name AN
46										Candidate Name 0015	Party Name AO
47										Candidate Name 0016	Party Name AP
48										Candidate Name 0017	Party Name AQ
49										Candidate Name 0018	Party Name AR
50										Candidate Name 0019	Party Name AS
51										Candidate Name 0020	Party Name AT
52										Candidate Name 0021	Party Name AU
53										Candidate Name 0022	Party Name AV
54										Candidate Name 0023	Party Name AW
55										Candidate Name 0024	Party Name AX
56										Candidate Name 0025	Party Name AY
57										Candidate Name 0026	Party Name AZ
58										Candidate Name 0027	Party Name BA
59										Candidate Name 0028	Party Name BB
60										Candidate Name 0029	Party Name BC
61										Candidate Name 0030	Party Name BD
62	NA-003	Province-1	Name-003	Area-003						Candidate Name 0001	Party Name AA
63										Candidate Name 0002	Party Name AB
64										Candidate Name 0003	Party Name AC
65										Candidate Name 0004	Party Name AD
66										Candidate Name 0005	Party Name AE
67										Candidate Name 0006	Party Name AF
68										Candidate Name 0007	Party Name AG
69										Candidate Name 0008	Party Name AH
70										Candidate Name 0009	Party Name AI
71										Candidate Name 0010	Party Name AJ
72										Candidate Name 0011	Party Name AK
73										Candidate Name 0012	Party Name AL
74										Candidate Name 0013	Party Name AM
75										Candidate Name 0014	Party Name AN
76										Candidate Name 0015	Party Name AO
77										Candidate Name 0016	Party Name AP
78										Candidate Name 0017	Party Name AQ
79										Candidate Name 0018	Party Name AR
80										Candidate Name 0019	Party Name AS
81										Candidate Name 0020	Party Name AT
82										Candidate Name 0021	Party Name AU
83										Candidate Name 0022	Party Name AV
84										Candidate Name 0023	Party Name AW
85										Candidate Name 0024	Party Name AX
86										Candidate Name 0025	Party Name AY
87										Candidate Name 0026	Party Name AZ
88										Candidate Name 0027	Party Name BA
89										Candidate Name 0028	Party Name BB
90										Candidate Name 0029	Party Name BC
91										Candidate Name 0030	Party Name BD
Election 2022 (MNA)

Cell Formulas
Range	Formula
F2	F2	=H2+G2
H2	H2	=SUM(L2:L31)
I2	I2	=IF(E2="","",F2/E2)
M2:M31	M2	=IF(L2="","",L2/$H$2)
N2	N2	=INDEX(J2:J31,MATCH(MAX(L2:L31),L2:L31,0))
O2	O2	=INDEX(K2:K31,MATCH(MAX(L2:L31),L2:L31,0))
P2	P2	=INDEX(J2:J31,MATCH(LARGE($L$2:$L$31,2),L2:L31,0))
Q2	Q2	=INDEX(K2:K31,MATCH(LARGE($L$2:$L$31,2),L2:L31,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M31	Expression	=OR(L2=MAX(L$2:L$31),L2=LARGE(L$2:L$31,1))	text	NO
L2:M31	Expression	=OR(L2=MAX(L$2:L$31),L2=LARGE(L$2:L$31,2))	text	NO
F2:F31	Cell Value	>$E$2	text	NO
F2:F31	Cell Value	>0	text	NO
H2:H31	Cell Value	>$E$2	text	NO

there are 3 records in this sheet at this moment but actually, it will be more than 250 to 300 records.
I worked with the first entry and it worked fine for all the formulas and condition checks.
but when I copy the first record and apply it to the rest of the sheet, I am not getting similar results as I get in the first entry. in fact, I get errors in the first record as well once I copy the first record to the rest of the sheet.
this is the temporary sheet as I don't have the actual data. for each record, I have temporarily given 30 candidates which might be increased or decreased when I get the actual data. but I have to prepare it now. so I used the maximum expected numbers.
please check what is going wrong and why the condition checks are not working when I copy them to the below records.
thank you and waiting for your response.

 

[KRice]

What version of Excel are you using? Please update your profile to show that, as some functions available in Excel 365 might prove useful, either now or in the future...so displaying your version helps others identify solutions that might work better for you.

One thing that jumps out is the extensive use of merged cells in columns A:I and N:Y. Generally it's a bad idea to use them, especially when formulas are involved. Some formulas misbehave with merged cells and it becomes difficult to follow what the formula is doing, Additionally, table filtering and sorting will not be possible. Two other issues:

1. The column P formula uses a fixed reference:

   LARGE($L$2:$L$31,2)

   This fixed reference pointing to $L$2:$L$31 will be carried forward to other record sets when you copy and paste. You probably want a relative reference, such as

   LARGE(L2:L31,2)

2. Same for column Q formula

Let me know about your Excel version. I'm thinking that a flat table might be more robust.
Are you opposed to multiple tables? One table would log voting region (districts, precincts, or provinces) statistics. Another logs statistics for candidates, and another summarizes winners by voting region. That way you wouldn't need merged cells, and you would avoid a lot of redundant information in a large table (which is what will happen if the current merged cell information is copied throughout each "province" block to eliminate the merged cells).