I have the following excerpt from a large Excel file (30K lines) and I need to tally the votes, where each voter could've voted for up to 3 candidates out of a list of 70.
col_1 col_2 col_3 vote_status
16 0 0 Invalid - Incorrect ballot number
31 25 66 Success
35 25 66 Valid
35 0 25 Valid - duplicate candidates
Looking at this data, you will see that the first example, the particular voter voted for candidate number 16 in the first column, and nobody in the second and third columns. However, due to the status of this vote, the vote should not count. There are three possible valid statuses: "Success", "Valid" and "Valid - duplicate candidates".
The voter in row 2 voted for candidate 31 in column 1, candidate 25 in column 2 and candidate 66 in column 3. The voter in row 3 is essentially the same is the previous, except that he voted for candidate 35 in column 1. The duplicate candidate in the fourth vote was candidate 35, and at voting time was replaced by a 0, so that it will be excluded from the count, but the other two candidate votes on that line is valid.
How can I generate a results list using Excel in the following format:
candidate col_1 col_2 col_3
16 0 0 0
25 0 2 1
31 1 0 0
35 2 0 0
66 0 0 2
The results do not even have to be in columns, if it will be easier to do, as long as the results are correct, namely: 0 votes for 16, 3 votes for 25, 1 vote for 31, 2 votes for 35 and 3 votes for 66.
I tried various combinations of sumif, countif, sumifs, countifs and sumproduct functions, but to no avail.
Hope you can help?
I'll do one of them for you, the rest should be pretty much the same. I'm working on candidate 16 and I'm going to assume that the word "candidate" is cell A1 on sheet "Sheet2" and the "Sheet1" is exactly what you posted above.
The contents of cell B2 is:
=COUNTIFS(Sheet1!A:A,Sheet2!$A2,Sheet1!$D:$D,"Success") + COUNTIFS(Sheet1!A:A,Sheet2!$A2,Sheet1!$D:$D,"Valid") + COUNTIFS(Sheet1!A:A,Sheet2!$A2,Sheet1!$D:$D,"Valid - duplicate candidates")
It's the sum of three separate countifs with two conditions each. The condition on the candidate and then on the status. The candidate condition is the same on each of them, the vote status is different.