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]

Thank you for the explanation. So you have multiple two-letter codes: NA, PP, PS, PB, KP...is that correct?
Within each common two-letter code (say "NA"), you have a common set of candidates for each NA#, correct?
But are the sets of candidates different between any two two-letter codes. For example if Candidates named "a", "b", and "c" appear in all of the NA# groups, you would not find the same candidates "a", "b", and "c" in the PP# groups, correct? Are the candidates different between NA and PP and PS and...

In your post #19 above, you have begun to duplicate the list of candidates in W:X. That is not what the worksheet was designed for. W:X should have only a unique list of candidates for one set of two-letter codes that appear in column A. So if you wanted to see a vote tabulation table created for 80 NA#'s, you would have NA-001 through NA-280 going down column A, one entry for each row. And if you had 30 candidates, each of whom should appear for every NA#, the W:X would have only 30 candidates listed. The formulas in J:M, when pulled down the page, will automatically create the combination table consisting of NA# and candidate/party name.

You could duplicate this worksheet, one for every two-letter code: NA on one sheet, PP on another sheet, etc.

Regarding the determination of a winner and runner-up. The formulas determine a winner for each NA#. So if you have 280 NA#'s, there will be 280 results. Are you also trying to determine which candidate has the most votes across all of the NA#'s?

 

[KRice]

So if you wanted to see a vote tabulation table created for 80 NA#'s, you would have NA-001 through NA-280 going down column A,

Sorry for the typo...I meant "...table created for 280 NA#'s..."

Also, please let me know about the answer to the last question. The point of that question is to understand whether you want to report back which single candidate received the most total votes assuming all of their votes were summed across all of the NA#'s. I don't see any indication in your original sheet that that is needed, but I wanted to confirm.

 

[mwvirk]

So you have multiple two-letter codes: NA, PP, PS, PB, KP...is that correct?

correct.

Within each common two-letter code (say "NA"), you have a common set of candidates for each NA#, correct?

within each common 2-letter code (say NA) I will have multiple candidates with different names and different party names but there are 100% possibilities that some of the candidates will be marked are "Independent" meaning they will not be a part of any party but if he/she wins then the winner can join any party (I am not counting it in my sheet) (for me it will be Indepantant only)
so, let's say I have NA-001 - so the candidate's names and parties will be as below (just an example)
Name Party
John Wright PARTY-ABC
Joe Biden PARTY-DEF
Michael JR PARTY-GHI
Mark Obama Independent
Steven Clark Independent
Steven JR Independent

but there are possibilities that I will have another candidate with the same name (since we take only first and second names) but in different parties. so add 1 more name to the above list for NA-001
Steven JR PARTY-JKL

(((Steven JR Independent - Steven JR PARTY-JKL <<<< only the names are the same but different person and in different party)))

now let us assume the NA-002
Name Party
Michael Wright PARTY-ABC
Joel JR PARTY-DEF
Michael John PARTY-GHI
Mark White Independent
Steven John Independent
Steven Michael Independent

another scenario is that Michael Wright from party PARTY-ABC is participating in NA-002 but the same person can also participate in NA-003 etc

But are the sets of candidates different between any two two-letter codes. For example if Candidates named "a", "b", and "c" appear in all of the NA# groups, you would not find the same candidates "a", "b", and "c" in the PP# groups, correct? Are the candidates different between NA and PP and PS and...

The person who is participating from NA can also participate from any other NA and even multiple times. 2 3 4 5 6 (just assume up to 10 different NA)
person who is participating from NA, the same person can also be a part of PP PS PB KP
The person who is participating from any 2-letter code can be anywhere with multiple times

Are you also trying to determine which candidate has the most votes across all of the NA#'s?

no. not the candidates but I am calculating each party is having the most votes from the actual valid votes and then doing the % comparison (it will be in my dashboard)

 

[mwvirk]

dashboard will appear like this:

 

[mwvirk]

it will be a lot of work and I don't want to keep on disturbing you.
as I said, my initial sheet is working perfectly but the only problem is the condition check to mark the highest voters and the runner-up. I can ignore this as well but it will be easy to find that candidate when looking at the results.
so if you can only help me to fix the L & M condition check, then we can resolve this case.
thank you once again

Election Test-0005.xlsx
	L	M
2	1,000	0.15%
3	100,000	15.04%
4	1,000	0.15%
5	1,000	0.15%
6	1,000	0.15%
7	1,000	0.15%
8	120,000	18.05%
9	1,000	0.15%
10	1,000	0.15%
11	1,000	0.15%
12	1,000	0.15%
13	130,000	19.55%
14	140,000	21.05%
15	150,000	22.56%
16	1,000	0.15%
17	1,000	0.15%
18	1,000	0.15%
19	1,000	0.15%
20	1,000	0.15%
21	1,000	0.15%
22	1,000	0.15%
23	1,000	0.15%
24	1,000	0.15%
25	1,000	0.15%
26	1,000	0.15%
27	1,000	0.15%
28	1,000	0.15%
29	1,000	0.15%
30	1,000	0.15%
31	1,000	0.15%
32	1,000	0.15%
33	1	0.00%
34	1	0.00%
35	160,000	24.06%
36	1,000	0.15%
37	170,000	25.56%
38	140,000	21.05%
39	130,000	19.55%
40	120,000	18.05%
41	180,000	27.07%
42	1,000	0.15%
43	1,000	0.15%
44	1,000	0.15%
45	1,000	0.15%
46	1,000	0.15%
47	1,000	0.15%
48	1,000	0.15%
49	1,000	0.15%
50	1,000	0.15%
51	1,000	0.15%
52	1,000	0.15%
53	1,000	0.15%
54	1,000	0.15%
55	1,000	0.15%
56	1,000	0.15%
57	1,000	0.15%
58	1,000	0.15%
59	1,000	0.15%
60	1,000	0.15%
61	1,000	0.15%
Election 2022 (MNA)

Cell Formulas
Range	Formula
M2	M2	=IF($L2="","",$L2/$H2)
M3:M31	M3	=IF($L3="","",$L3/$H$2)
M32:M61	M32	=IF(L32="","",L32/$H$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L32:M61	Expression	=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,1))	text	NO
L32:M61	Expression	=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,2))	text	NO
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

 

[mwvirk]

if we can use the unique 2-letter code in the condition check then it will be more than enough but if it's possible.

 

[KRice]

There are some problems with your Conditional Formatting formulas. Let's take the first one as an example:

=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,1))

Note that the range L$2:L$31 is being examined...we want the max for range L$2:L$31 but the formula is checking whether L32 (not in the same range) is equal to the max. That isn't correct.
Another comment: the formula uses an OR condition, so if the largest or second largest values are found, the same action will be taken. That isn't what you want. My understanding is that you want the largest vote tally to be filled green and the 2nd largest to be filled red. So you would need two separate formulas to do this: one checks for the largest value, and the other formula checks for the 2nd largest value.
This formula illustrates why I think using merged cells and not including the two-letter code on each row for each candidate is a problem. You have no good way to construct a formula that will consider only the relevant votes for the NA# being considered. As your worksheet is currently structured, you would need two formulas for each NA# that look something like this:

=LARGE(L$2:L$31,1)=$L2

...if TRUE then fill cell green

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

...if TRUE then fill cell red
...then for the next NA#, the conditional formatting would look like this:

=LARGE(L$32:L$61,1)=$L32

...if TRUE then fill cell green

=LARGE(L$32:L$61,2)=$L32

...if TRUE then fill cell red
This is tedious to maintain and very cumbersome. Every block having a common NA# needs to have its range declared in the formula. I wouldn't do it this way. And if the number of candidates can vary among the different NA#'s, you couldn't rely on a pattern.

Instead, I would recommend adding a column between columns I and J that shows the NA# for each candidate. Then you could write two Conditional Formatting formulas and apply them to the entire column for [Votes] and [% of Candidate Votes]. Those formulas would look like this:

=AGGREGATE(14,6,($M$2:$M$1000)/(($J$2:$J$1000=$J2)*($J2<>"")*($M2<>"")),1)=$M2

...if TRUE then fill cell green

=AGGREGATE(14,6,($M$2:$M$1000)/(($J$2:$J$1000=$J2)*($J2<>"")*($M2<>"")),2)=$M2

...if TRUE then fill cell red
These formulas look at the entire vote tabulation column (adjust the last row to cover the entire table) and confirm that the [Two-letter-#] column entry is not blank and the [Votes] column entry is not blank, then it aggregates all votes having a common [Two-letter-#] code and determines the largest (green) and 2nd largest (red) vote tally.

Mrexcel_20220917a.xlsx
	J	K	L	M	N
1	Two-letter-#	Candidate Name	Party Name	Votes	% Of Candidate Votes
2	NA-001	Candidate Name 0001	Party Name AA	1000	0.02311
3	NA-001	Candidate Name 0002	Party Name AB	500	0.011555
4	NA-001	Candidate Name 0003	Party Name AC	2	4.62E-05
5	NA-002	Candidate Name 0004	Party Name AD	9000	0.207987
6	NA-002	Candidate Name 0005	Party Name AE	4	9.24E-05
7	NA-002	Candidate Name 0006	Party Name AF	2700	0.062396
8	NA-002	Candidate Name 0007	Party Name AG	6	0.000139
9	NA-003	Candidate Name 0008	Party Name AH	7	0.000162
10	NA-003	Candidate Name 0009	Party Name AI	2700	0.062396
11	NA-003	Candidate Name 0010	Party Name AJ	10	0.000231
12	NA-003	Candidate Name 0011	Party Name AK	11	0.000254
13	NA-003	Candidate Name 0012	Party Name AL	12	0.000277
14	NA-003	Candidate Name 0013	Party Name AM	13	0.0003
Election 2022 (MNA) (4)

Cell Formulas
Range	Formula
N2:N14	N2	=IF(M2="","",M2/$H$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:N14	Expression	=AGGREGATE(14,6,($M$2:$M$1000)/(($J$2:$J$1000=$J2)*($J2<>"")*($M2<>"")),1)=$M2	text	NO
M2:N14	Expression	=AGGREGATE(14,6,($M$2:$M$1000)/(($J$2:$J$1000=$J2)*($J2<>"")*($M2<>"")),2)=$M2	text	NO

 

[mwvirk]

perfect.... excellent work.

applied the last formulas after adding the NA# or each candidate and it's working as it should work.
thank you so much, dude. really appreciate it.

I am sharing the screenshot and a mini sheet. actually, I have tested in 10 records but just sharing the 3 records as a mini sheet.
there are green error arrows in O P Q R - could you please check these? (you can find them on the upper left edge of the cell O P Q R

also please test the mini sheet if I did everything perfectly.

best regards,

Election 2022.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	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
2	318,150	3,071	500	2,571	0.97%	NA-001	Candidate Name 0001	Party Name AA	10	0.39%	Candidate Name 0012	Party Name AL	Candidate Name 0005	Party Name AE
3						NA-001	Candidate Name 0002	Party Name AB	20	0.78%
4						NA-001	Candidate Name 0003	Party Name AC	200	7.78%
5						NA-001	Candidate Name 0004	Party Name AD	40	1.56%
6						NA-001	Candidate Name 0005	Party Name AE	500	19.45%
7						NA-001	Candidate Name 0006	Party Name AF	60	2.33%
8						NA-001	Candidate Name 0007	Party Name AG	70	2.72%
9						NA-001	Candidate Name 0008	Party Name AH	80	3.11%
10						NA-001	Candidate Name 0009	Party Name AI	90	3.50%
11						NA-001	Candidate Name 0010	Party Name AJ	100	3.89%
12						NA-001	Candidate Name 0011	Party Name AK	101	3.93%
13						NA-001	Candidate Name 0012	Party Name AL	600	23.34%
14						NA-001	Candidate Name 0013	Party Name AM	0	0.00%
15						NA-001	Candidate Name 0014	Party Name AN	0	0.00%
16						NA-001	Candidate Name 0015	Party Name AO	0	0.00%
17						NA-001	Candidate Name 0016	Party Name AP	0	0.00%
18						NA-001	Candidate Name 0017	Party Name AQ	0	0.00%
19						NA-001	Candidate Name 0018	Party Name AR	0	0.00%
20						NA-001	Candidate Name 0019	Party Name AS	0	0.00%
21						NA-001	Candidate Name 0020	Party Name AT	0	0.00%
22						NA-001	Candidate Name 0021	Party Name AU	0	0.00%
23						NA-001	Candidate Name 0022	Party Name AV	0	0.00%
24						NA-001	Candidate Name 0023	Party Name AW	0	0.00%
25						NA-001	Candidate Name 0024	Party Name AX	0	0.00%
26						NA-001	Candidate Name 0025	Party Name AY	300	11.67%
27						NA-001	Candidate Name 0026	Party Name AZ	0	0.00%
28						NA-001	Candidate Name 0027	Party Name BA	0	0.00%
29						NA-001	Candidate Name 0028	Party Name BB	0	0.00%
30						NA-001	Candidate Name 0029	Party Name BC	0	0.00%
31						NA-001	Candidate Name 0030	Party Name BD	400	15.56%
32		1,500	500	1,000		NA-002	Candidate Name 0001	Party Name AA	0	0.00%	Candidate Name 0025	Party Name AY	Candidate Name 0012	Party Name AL
33						NA-002	Candidate Name 0002	Party Name AB	0	0.00%
34						NA-002	Candidate Name 0003	Party Name AC	0	0.00%
35						NA-002	Candidate Name 0004	Party Name AD	0	0.00%
36						NA-002	Candidate Name 0005	Party Name AE	0	0.00%
37						NA-002	Candidate Name 0006	Party Name AF	0	0.00%
38						NA-002	Candidate Name 0007	Party Name AG	0	0.00%
39						NA-002	Candidate Name 0008	Party Name AH	0	0.00%
40						NA-002	Candidate Name 0009	Party Name AI	0	0.00%
41						NA-002	Candidate Name 0010	Party Name AJ	0	0.00%
42						NA-002	Candidate Name 0011	Party Name AK	0	0.00%
43						NA-002	Candidate Name 0012	Party Name AL	300	11.67%
44						NA-002	Candidate Name 0013	Party Name AM	0	0.00%
45						NA-002	Candidate Name 0014	Party Name AN	0	0.00%
46						NA-002	Candidate Name 0015	Party Name AO	0	0.00%
47						NA-002	Candidate Name 0016	Party Name AP	100	3.89%
48						NA-002	Candidate Name 0017	Party Name AQ	200	7.78%
49						NA-002	Candidate Name 0018	Party Name AR	0	0.00%
50						NA-002	Candidate Name 0019	Party Name AS	0	0.00%
51						NA-002	Candidate Name 0020	Party Name AT	0	0.00%
52						NA-002	Candidate Name 0021	Party Name AU	0	0.00%
53						NA-002	Candidate Name 0022	Party Name AV	0	0.00%
54						NA-002	Candidate Name 0023	Party Name AW	0	0.00%
55						NA-002	Candidate Name 0024	Party Name AX	0	0.00%
56						NA-002	Candidate Name 0025	Party Name AY	400	15.56%
57						NA-002	Candidate Name 0026	Party Name AZ	0	0.00%
58						NA-002	Candidate Name 0027	Party Name BA	0	0.00%
59						NA-002	Candidate Name 0028	Party Name BB	0	0.00%
60						NA-002	Candidate Name 0029	Party Name BC	0	0.00%
61						NA-002	Candidate Name 0030	Party Name BD	0	0.00%
62		320,551	500	320,051		NA-003	Candidate Name 0001	Party Name AA	0	0.00%	Candidate Name 0004	Party Name AD	Candidate Name 0003	Party Name AC
63						NA-003	Candidate Name 0002	Party Name AB	10	0.39%
64						NA-003	Candidate Name 0003	Party Name AC	20,000	777.91%
65						NA-003	Candidate Name 0004	Party Name AD	300,000	11668.61%
66						NA-003	Candidate Name 0005	Party Name AE	0	0.00%
67						NA-003	Candidate Name 0006	Party Name AF	0	0.00%
68						NA-003	Candidate Name 0007	Party Name AG	0	0.00%
69						NA-003	Candidate Name 0008	Party Name AH	0	0.00%
70						NA-003	Candidate Name 0009	Party Name AI	0	0.00%
71						NA-003	Candidate Name 0010	Party Name AJ	0	0.00%
72						NA-003	Candidate Name 0011	Party Name AK	0	0.00%
73						NA-003	Candidate Name 0012	Party Name AL	0	0.00%
74						NA-003	Candidate Name 0013	Party Name AM	0	0.00%
75						NA-003	Candidate Name 0014	Party Name AN	0	0.00%
76						NA-003	Candidate Name 0015	Party Name AO	0	0.00%
77						NA-003	Candidate Name 0016	Party Name AP	0	0.00%
78						NA-003	Candidate Name 0017	Party Name AQ	0	0.00%
79						NA-003	Candidate Name 0018	Party Name AR	0	0.00%
80						NA-003	Candidate Name 0019	Party Name AS	0	0.00%
81						NA-003	Candidate Name 0020	Party Name AT	0	0.00%
82						NA-003	Candidate Name 0021	Party Name AU	0	0.00%
83						NA-003	Candidate Name 0022	Party Name AV	0	0.00%
84						NA-003	Candidate Name 0023	Party Name AW	0	0.00%
85						NA-003	Candidate Name 0024	Party Name AX	20	0.78%
86						NA-003	Candidate Name 0025	Party Name AY	21	0.82%
87						NA-003	Candidate Name 0026	Party Name AZ	0	0.00%
88						NA-003	Candidate Name 0027	Party Name BA	0	0.00%
89						NA-003	Candidate Name 0028	Party Name BB	0	0.00%
90						NA-003	Candidate Name 0029	Party Name BC	0	0.00%
91						NA-003	Candidate Name 0030	Party Name BD	0	0.00%
Election 2022 (MNA)

Cell Formulas
Range	Formula
H2,H62,H32	H2	=SUM(M2:M31)
I2,I62,I32	I2	=IF(E2="","",F2/E2)
N2:N91	N2	=IF(M2="","",M2/$H$2)
O2,O62,O32	O2	=INDEX(K2:K31,MATCH(MAX(M2:M31),M2:M31,0))
P2,P62,P32	P2	=INDEX(L2:L31,MATCH(MAX(M2:M31),M2:M31,0))
Q2,Q62,Q32	Q2	=INDEX(K2:K31,MATCH(LARGE($M2:$M31,2),M2:M31,0))
R2,R62,R32	R2	=INDEX(L2:L31,MATCH(LARGE($M2:$M31,2),M2:M31,0))
F2,F62,F32	F2	=H2+G2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F32:F301	Cell Value	>$E$2	text	NO
F32:F301	Cell Value	>0	text	NO
H32:H301	Cell Value	>$E$2	text	NO
M3:N301	Expression	=AGGREGATE(14,6,($M$2:$M$1000)/(($J$2:$J$1000=$J3)*($J3<>"")*($M3<>"")),1)=$M3	text	NO
M3:N301	Expression	=AGGREGATE(14,6,($M$2:$M$1000)/(($J$2:$J$1000=$J3)*($J3<>"")*($M3<>"")),2)=$M3	text	NO
M2:N2	Expression	=AGGREGATE(14,6,($M$2:$M$1000)/(($J$2:$J$1000=$J2)*($J2<>"")*($M2<>"")),1)=$M2	text	NO
M2:N2	Expression	=AGGREGATE(14,6,($M$2:$M$1000)/(($J$2:$J$1000=$J2)*($J2<>"")*($M2<>"")),2)=$M2	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

 

[mwvirk]

defenitely I need help while preparing the dashboard.
let me start entering the dummy data for all NA# PP# PS# PB# KP# and then I will start the dummy voting system, and based on that, I will prepare the dashboard. but before that, you can verify the above mini sheet and clear the error, please.

 

[KRice]

If you examine my suggested worksheet in post #16, you will see that I recommend finding the [Valid Cast Votes] (col. H), and the [Winner's Name] (col. O), [Winner Party] (col. P), [Runner up Name] (col. Q), and [Runner up Party] (col. R) differently. The reason for a different approach is the same one described in post #27...

This is tedious to maintain and very cumbersome. Every block having a common NA# needs to have its range declared in the formula. I wouldn't do it this way. And if the number of candidates can vary among the different NA#'s, you couldn't rely on a pattern.

So instead of finding the [Winner's Name} with this:

=INDEX(K2:K31,MATCH(MAX(M2:M31),M2:M31,0))

...and then having to worry about adjusting ranges (the K2:K31 and M2:M31) in every section where the two-letter # combination changes, it makes more sense to use the newly added column J to perform a filter-type operation on the full column. That way a single formula can be used without adjusting the ranges. This idea is applied everywhere else to avoid specifying two-letter #-specific ranges. Assuming you keep the merged rows in column A to show the two-letter # for each particular block (again, I don't recommend this, but that seems to be your preference), then the first block would look something like this:

Mrexcel_20220917a.xlsx
	A				E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	NA # (2018)				Registered Voters	Total Cast Votes	Rejected Votes	Valid Cast Votes	% Of Total Registered Votes	2-ltr-#	Candidate Name	Party Name	Votes	% Of Candidate Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party
2	NA-001				318150	3207	500	2707	0.01008015	NA-001	Candidate Name 0001	Party Name AA	10	0.37%	Candidate Name 0012	Party Name AL	Candidate Name 0005	Party Name AE
3										NA-001	Candidate Name 0002	Party Name AB	20	0.74%
4										NA-001	Candidate Name 0003	Party Name AC	200	7.39%
5										NA-001	Candidate Name 0004	Party Name AD	40	1.48%
6										NA-001	Candidate Name 0005	Party Name AE	500	18.47%
7										NA-001	Candidate Name 0006	Party Name AF	60	2.22%
8										NA-001	Candidate Name 0007	Party Name AG	70	2.59%
9										NA-001	Candidate Name 0008	Party Name AH	80	2.96%
10										NA-001	Candidate Name 0009	Party Name AI	90	3.32%
11										NA-001	Candidate Name 0010	Party Name AJ	100	3.69%
12										NA-001	Candidate Name 0011	Party Name AK	101	3.73%
13										NA-001	Candidate Name 0012	Party Name AL	600	22.16%
14										NA-001	Candidate Name 0013	Party Name AM	1	0.04%
15										NA-001	Candidate Name 0014	Party Name AN	2	0.07%
16										NA-001	Candidate Name 0015	Party Name AO	3	0.11%
17										NA-001	Candidate Name 0016	Party Name AP	4	0.15%
18										NA-001	Candidate Name 0017	Party Name AQ	5	0.18%
19										NA-001	Candidate Name 0018	Party Name AR	6	0.22%
20										NA-001	Candidate Name 0019	Party Name AS	7	0.26%
21										NA-001	Candidate Name 0020	Party Name AT	8	0.30%
22										NA-001	Candidate Name 0021	Party Name AU	9	0.33%
23										NA-001	Candidate Name 0022	Party Name AV	10	0.37%
24										NA-001	Candidate Name 0023	Party Name AW	11	0.41%
25										NA-001	Candidate Name 0024	Party Name AX	12	0.44%
26										NA-001	Candidate Name 0025	Party Name AY	300	11.08%
27										NA-001	Candidate Name 0026	Party Name AZ	13	0.48%
28										NA-001	Candidate Name 0027	Party Name BA	14	0.52%
29										NA-001	Candidate Name 0028	Party Name BB	15	0.55%
30										NA-001	Candidate Name 0029	Party Name BC	16	0.59%
31										NA-001	Candidate Name 0030	Party Name BD	400	14.78%
Sheet4

Cell Formulas
Range	Formula
F2	F2	=IF(H2="","",H2+G2)
H2	H2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),SUMIF($J:$J,$A2,$M:$M),"")
I2	I2	=IF(OR(E2="",F2=""),"",F2/E2)
N2:N31	N2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),$M2/SUMIF($J:$J,$J2,$M:$M),"")
O2	O2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),INDEX($K$2:$K$1000,MATCH(1,($J$2:$J$1000=$A2)*($M$2:$M$1000=LARGE(($M$2:$M$1000)*($J$2:$J$1000=$A2),1)),0)),"")
P2	P2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),INDEX($L$2:$L$1000,MATCH(1,($J$2:$J$1000=$A2)*($M$2:$M$1000=LARGE(($M$2:$M$1000)*($J$2:$J$1000=$A2),1)),0)),"")
Q2	Q2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),INDEX($K$2:$K$1000,MATCH(1,($J$2:$J$1000=$A2)*($M$2:$M$1000=LARGE(($M$2:$M$1000)*($J$2:$J$1000=$A2),2)),0)),"")
R2	R2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),INDEX($L$2:$L$1000,MATCH(1,($J$2:$J$1000=$A2)*($M$2:$M$1000=LARGE(($M$2:$M$1000)*($J$2:$J$1000=$A2),2)),0)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:N31	Expression	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),AGGREGATE(14,6,($M$2:$M$1000)/($J$2:$J$1000=$J2),1)=$M2,FALSE)	text	NO
M2:N31	Expression	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),AGGREGATE(14,6,($M$2:$M$1000)/($J$2:$J$1000=$J2),2)=$M2,FALSE)	text	NO

Note that the formulas have been revised to perform some checking that vote tallies have been entered for an entire two-letter # block before returning results. This is to avoid errors and misunderstandings should someone look at an incomplete voting tabulation and see a winner/runner-up before all of the votes have been entered. As the table grows in length, you will need to adjust these formulas to cover the entire length of the table (it's okay if the formula refers to rows far below the end of the data)...but I expect the references to row 1000 will change in the in-cell formulas and in the Data Validation formulas.

You will want to try out this worksheet with a large set of data to see if it behaves as expected and if the performance is too slow. The conditional formatting and the checks to confirm that data have been entered before rendering a result may adversely affect performance.

Regarding the error messages, if you click on those cells with the green flags, you will see a yellow symbol with a dropdown arrow. Selecting the dropdown reveals the error is due to "Formula Omits Adjacent Cells". This is an artifact of your choice to use merged cells. The formulas are in cells O2:R2 (although I have to scroll all the way down to rows 15:17 to see the formula results); and the formulas refer to ranges in rows 2:31...and then there is nothing below the formula until row 32, where the next group of formulas is found. There is nothing inherently wrong that the errors are flagging...they are simply calling attention to the fact that cells around the formula do not have formulas in them. If you continue to see them, you can go back into each one, click the dropdown I described and select the option to ignore this error.

also please test the mini sheet if I did everything perfectly

I hesitate to say that everything is done perfectly. I think the worksheet will prove to be difficult to use and difficult to extract summary information from, but you or others will need to make that determination. That said, I believe the formulas return the correct results, are reasonably robust, and handle the issues created by the merged cells satisfactorily. I suppose if you wanted a summary table of results that did not include the vote tabulation table (so that the results were more consolidated), you could either apply column auto-filters to hide the blank rows (created by the merged cells), or use another set of formulas to extract the first row of every common two-letter code block...but that seems like more work than necessary.