Election results

[mwvirk]

please help me to solve these issues. it's not VBA and i can't find any other thread to post it in this forum.

please help me in row 20 21 22 (i'm highlighting it yellow/red)
you can see the formula in row 20 but these are not working. in row 21/22 i have given my comments and expected results where results in row 20 are absolutely wrong.

thanks.

formulas which are not returning exact results:

K20
My Formula:
=SUMPRODUCT((C2:C16=M18)*(J2:J16=J20))+SUMPRODUCT((C2:C16=M18)*(M2:M16=J20) )+SUMPRODUCT((C2:C16=M18)*(P2:P16=J20))+SUMPRODUCT((C2:C16=M18)*(S2:S16=J20 ))+SUMPRODUCT((C2:C16=M18)*(V2:V16=J20))+SUMPRODUCT((C2:C16=M18)*(Y2:Y16=J2 0))



L20
My Formula:
=SUMPRODUCT(($C$2:$C$16=M18)*($I$2:$I$16<>"")*($J$2:$J$16=J20))+SUMPRODUCT(($C$2:$C$16=M18)*($L$2:$L$16<>"")*($M$2:$M$16=J20))+SUMPRODUCT(($C$2:$C$16=M18)*($O$2:$O$16<>"")*($P$2:$P$16=J20))+SUMPRODUCT(($C$2:$C$16=M18)*($R$2:$R$16<>"")*($S$2:$S$16=J20))+SUMPRODUCT(($C$2:$C$16=M18)*($U$2:$U$16<>"")*($V$2:$V$16=J20))+SUMPRODUCT(($C$2:$C$16=M18)*($X$2:$X$16<>"")*($Y$2:$Y$16=J20))



M20
My Formula:
=SUMPRODUCT((C2:C16=M18)*(AB2:AB16=J20))



N20
My Formula:
=SUMPRODUCT((C2:C16=M18)*(J2:J16=J20)*(AB2:AB16<>J20))



O20
My Formula:
=(K20-(M20+N20))



P20
My Formula:
=SUMIF((K2:K16,J20)+(n2:n16,J20)+(q2:q16,J20)+(t2:t16,J20)+(w2:w16,J20)+(z2 :z16,J20))


please try to shrink these formula. it's just a sample file with around 5 or 6 record. the actual file is having 50 record in each row

my actual goal is to only enter the vote that cast and get the entire results automated.

[p45cal]

Just to get you started, try changing instances of M18 in formulae to K18.
You're using the wrong reference to merged cells.

[p45cal]

Could your K20 formula be replaced by:
=SUMPRODUCT(($C$2:$C$16=$K$18)*(($J$2:$J$16=J20)+($M$2:$M$16=$J20)+($P$2:$P $16=$J20)+($S$2:$S$16=$J20)+($V$2:$V$16=$J20)+($Y$2:$Y$16=$J20)))
which can be copied down?

[mwvirk]

Could your K20 formula be replaced by:
=SUMPRODUCT(($C$2:$C$16=$K$18)*(($J$2:$J$16=J20)+($M$2:$M$16=$J20)+($P$2:$P $16=$J20)+($S$2:$S$16=$J20)+($V$2:$V$16=$J20)+($Y$2:$Y$16=$J20)))
which can be copied down?

great. it's working and returning what i have mentioned under this cell. question is, in this sample, i have only 6 records, how to handle when it's 50 records.
values in I J K is 1 entry. same way L M N is 2nd entry and O P Q is 3rd and so on and finally ending up after 50 entries.
could you please also help in other formulas.
Thank you.

[p45cal]

in this sample, i have only 6 records, how to handle when it's 50 records.
I'm thinking about it…

could you please also help in other formulas.
Thank you.try for L20:
=SUMPRODUCT(($C$2:$C$16=K18)*(($I$2:$I$16="")*($J$2:$J$16=$J20)+($L$2:$L$16="")*($M$2:$M$16=$J20)+($O$2:$O$16="")*($P$2:$P$16=$J20)+($R$2:$R$16="")*($S$2:$S$16=$J20)+($U$2:$U$16="")*($V$2:$V$16=$J20)+($X$2:$X$16="")*($Y$2:$Y$16=$J20)))

I'm calling it a day for now. You should be able to derive similar formulae for the other columns. Do what you can, attach another sheet, and I'll revisit later.

Regarding the layout of data; is that your own design? How are you getting this data into Excel and from where? I'm thining along the lines of a different layout, because as you see, it makes for long formulae.

[p45cal]

values in I J K is 1 entry. same way L M N is 2nd entry and O P Q is 3rd and so on and finally ending up after 50 entries.What do each of these entries represent. How come there are so many?

[mwvirk]

as you can see in heading, these 3 column are info for 1 candidate (Name, party name, votes cast)
so many entries are because 1 to 50 candidates are participating in 1 area.

[mwvirk]

I'm thinking about it…
try for L20:
=SUMPRODUCT(($C$2:$C$16=K18)*(($I$2:$I$16="")*($J$2:$J$16=$J20)+($L$2:$L$16="")*($M$2:$M$16=$J20)+($O$2:$O$16="")*($P$2:$P$16=$J20)+($R$2:$R$16="")*($S$2:$S$16=$J20)+($U$2:$U$16="")*($V$2:$V$16=$J20)+($X$2:$X$16="")*($Y$2:$Y$16=$J20)))

I'm calling it a day for now. You should be able to derive similar formulae for the other columns. Do what you can, attach another sheet, and I'll revisit later.

Regarding the layout of data; is that your own design? How are you getting this data into Excel and from where? I'm thining along the lines of a different layout, because as you see, it makes for long formulae.

above formula is still returning 0 rather than giving correct result '1'
file is attached.

[p45cal]

above formula is still returning 0 rather than giving correct result '1'.As far as I can see, all P-1s have a Participant (and not just for Prov-1 but for all assemblies). Temporarily delete the xyz from cell I4 and you'll see the value in L20 go up by 1.

If I'm wrong, could you point out where the 1 you expect is coming from?

[p45cal]

Regarding the layout of data; is that your own design? How are you getting this data into Excel and from where? I'm thinking along the lines of a different layout, because as you see, it makes for long formulae.
Could you also say something about the above?

[mwvirk]

Could you also say something about the above?
i can't send you PM:

To be able to send PMs your post count must be 10 or greater.
Your post count is 6 momentarily and you can send PMs to Staff only.

i want to share actual file in private and you can update the formulas in TEST file.

[mwvirk]

As far as I can see, all P-1s have a Participant (and not just for Prov-1 but for all assemblies). Temporarily delete the xyz from cell I4 and you'll see the value in L20 go up by 1.

If I'm wrong, could you point out where the 1 you expect is coming from?

Participant is just a temp name. it can be any name (John, peter, joe, maria etc)

[p45cal]

Participant is just a temp name. it can be any name (John, peter, joe, maria etc)I think I realise that, but it doesn't detract from it giving what I think is the correct answer. If you could point out which participant name (the cell address) is missing from the file you attached in Message#8, then I can go about trying to correct the formula - at the moment I can't do this unles I know what it's missing.

I will PM you with a private email address.

[mwvirk]

If I'm wrong, could you point out where the 1 you expect is coming from?

1 should come in L20.
what is L20 ?
if 1 province is having let say 10 seat. then out of 10, 1 of party (say P-1) is having how many candidate in that province, is will come in K20
support they have 8 candidate out of 10 then "candidate not available" in L20 will show 2 (not participating)
now M20 will show how many seat 1 paty has won
N20 will show the seat 1 party lost
O20 will show pending seat where results are not yet announced
P20 will calculate all the votes (win/lost) for that party in that province

[mwvirk]

sorry for the delay. i was making some change so you can make out some different as you might be surprised after looking at this huge data entry.

[p45cal]

1 should come in L20.
what is L20 ?
if 1 province is having let say 10 seat. then out of 10, 1 of party (say P-1) is having how many candidate in that province, is will come in K20
support they have 8 candidate out of 10 then "candidate not available" in L20 will show 2 (not participating)
Then perhaps this is the sort of formula in L20:
=COUNTIF($C$2:$C$16,K18)-$K20
Please confirm this is using the right logic.

(a bit of a mis-direction having all those columns looked at in your original L20 formula!)

[mwvirk]

Then perhaps this is the sort of formula in L20:
=COUNTIF($C$2:$C$16,K18)-$K20
Please confirm this is using the right logic.

(a bit of a mis-direction having all those columns looked at in your original L20 formula!)

please don't look for L20 in original file. L20 is part of summary which was mention below in test file. but in original file, this summary is on extreme right. (after runner up column)

[p45cal]

please don't look for L20 in original file. L20 is part of summary which was mention below in test file. but in original file, this summary is on extreme right. (after runner up column)Nevertheless, is it OK on the test file?

[mwvirk]

yes. because the logic you are using is simple because you are not searching in data entry part. with this simple formula, you are just counting based on other cell values.
can't fix it, the 1 you gave me before:

=SUMPRODUCT(($C$2:$C$16=K18)*(($I$2:$I$16="")*($J$2:$J$16=$J20)+($L$2:$L$16="")*($M$2:$M$16=$J20)+($O$2:$O$16="")*($P$2:$P$16=$J20)+($R$2:$R$16="")*($S$2:$S$16=$J20)+($U$2:$U$16="")*($V$2:$V$16=$J20)+($X$2:$X$16="")*($Y$2:$Y$16=$J20)))

[mwvirk]

this 1 i'm applying in my original file and it's returning 0 in cell FY13

=SUMPRODUCT(($C$2:$C$273=$FZ$11)*(($I$2:$I$273="")*($J$2:$J$273=$fx$13)+($L$2:$L$273="")*($M$2:$M$273=$fx$13)+($O$2:$O$273="")*($P$2:$P$273=$fx$13)+($R$2:$R$273="")*($S$2:$S$273=$fx$13)+($U$2:$U$273="")*($V$2:$V$273=$fx$13)+($X$2:$X$273="")*($Y$2:$Y$273=$fx$13)+($AA$2:$AA$273="")*($AB$2:$AB$273=$fx$13)+($AD$2:$AD$273="")*($AE$2:$AE$273=$fx$13)+($AG$2:$AG$273="")*($AH$2:$AH$273=$fx$13)+($AJ$2:$AJ$273="")*($AK$2:$AK$273=$fx$13)+($AM$2:$AM$273="")*($AN$2:$AN$273=$fx$13)+($AP$2:$AP$273="")*($AQ$2:$AQ$273=$fx$13)+($AS$2:$AS$273="")*($AT$2:$AT$273=$fx$13)+($AV$2:$AV$273="")*($AW$2:$AW$273=$fx$13)+($AY$2:$AY$273="")*($AZ$2:$AZ$273=$fx$13)+($BB$2:$BB$273="")*($BC$2:$BC$273=$fx$13)+($BE$2:$BE$273="")*($BF$2:$BF$273=$fx$13)+($BH$2:$BH$273="")*($BI$2:$BI$273=$fx$13)+($BK$2:$BK$273="")*($BL$2:$BL$273=$fx$13)+($BN$2:$BN$273="")*($BO$2:$BO$273=$fx$13)+($BQ$2:$BQ$273="")*($BR$2:$BR$273=$fx$13)+($BT$2:$BT$273="")*($BU$2:$BU$273=$fx$13)+($BW$2:$BW$273="")*($BX$2:$BX$273=$fx$13)+($BZ$2:$BZ$273="")*($CA$2:$CA$273=$fx$13)+($CC$2:$CC$273="")*($CD$2:$CD$273=$fx$13)+($CF$2:$CF$273="")*($CG$2:$CG$273=$fx$13)+($CI$2:$CI$273="")*($CJ$2:$CJ$273=$fx$13)+($CL$2:$CL$273="")*($CM$2:$CM$273=$fx$13)+($CO$2:$CO$273="")*($CP$2:$CP$273=$fx$13)+($CR$2:$CR$273="")*($CS$2:$CS$273=$fx$13)+($CU$2:$CU$273="")*($CV$2:$CV$273=$fx$13)+($CX$2:$CX$273="")*($CY$2:$CY$273=$fx$13)+($DA$2:$DA$273="")*($DB$2:$DB$273=$fx$13)+($DD$2:$DD$273="")*($DE$2:$DE$273=$fx$13)+($DG$2:$DG$273="")*($DH$2:$DH$273=$fx$13)+($DJ$2:$DJ$273="")*($DK$2:$DK$273=$fx$13)+($DM$2:$DM$273="")*($DN$2:$DN$273=$fx$13)+($DP$2:$DP$273="")*($DQ$2:$DQ$273=$fx$13)+($DS$2:$DS$273="")*($DT$2:$DT$273=$fx$13)+($DV$2:$DV$273="")*($DW$2:$DW$273=$fx$13)+($DY$2:$DY$273="")*($DZ$2:$DZ$273=$fx$13)+($EB$2:$EB$273="")*($EC$2:$EC$273=$fx$13)+($EE$2:$EE$273="")*($EF$2:$EF$273=$fx$13)+($EH$2:$EH$273="")*($EI$2:$EI$273=$fx$13)+($EK$2:$EK$273="")*($EL$2:$EL$273=$fx$13)+($EN$2:$EN$273="")*($EO$2:$EO$273=$fx$13)+($EQ$2:$EQ$273="")*($ER$2:$ER$273=$fx$13)+($ET$2:$ET$273="")*($EU$2:$EU$273=$fx$13)+($EW$2:$EW$273="")*($EX$2:$EX$273=$fx$13)+($EZ$2:$EZ$273="")*($FA$2:$FA$273=$fx$13)+($FC$2:$FC$273="")*($FD$2:$FD$273=$fx$13)))