I occasionally create fictional elections for fun and experiment with different methods of doing them. This time, I did an election for the presidency of a country, and I planned to carry it out with a Condorcet method. For those who aren’t familiar, a Condorcet method is any election system that declares the candidate who can beat all of their competitors, or more of the other candidates than anyone else, the winner. This winner is the Condorcet winner. I created a way to calculate who would be the Condorcet winner with a bunch of tables within my spreadsheet.

Here’s an image to help explain things, the first of two. It contains the row and column values.

This first table contains several columns, corresponding to the election candidates and their supporters. Each candidate’s supporters have distinct preferences for which candidate they would like to win. The candidate who is named in the top rows is the first preference of their voters. Beneath them are all the other candidates. At the bottom is the number of voters whose first preference is the candidate whose name is at the top of the column.

Beneath that is another table. It contains all of the votes each candidate would get, based on the preferences in the first table, if they were pitted against each other in a series of one-on-one contests. The candidate whose name is listed in every column at the top of the spreadsheet is pitted against the candidates whose names are in the rows on the left. In each cell within each column in the imaged table, the votes for the candidate listed at the top of the column are counted up. There’s a conditional formatting rule that turns the cell blue if the candidate has a majority of votes. Beneath all the candidates are a set of four rows. The first row lists how many times they got a majority of votes, the second lists the average number of votes they received, and the last one ranks them in descending order, with the candidate with the most victories getting the rank of 1. Where a candidate would be pitted against themself, that cell is represented with “Candidate vs self” in markdown.

The votes are tallied up as follows: every candidate receives the votes of each group of voters who prefer them over their opponent, according to the preferences in the first table. Originally, I did this manually. I went through each time and summed up the number of votes. It got very exhausting, taking about two hours to carry out. And this was for only one situation where the votes fell in a certain way! I did it manually once again with slightly different vote counts, which took a few more hours. I started to think of writing a formula that could handle it all for me.

I poked at several things under query(), lookup(), and vlookup(), none of which worked. I learned that none of them supported looking for matches in multiple cells across multiple rows and columns, or if they did, it was beyond my power to learn whether or not they did so tonight. Eventually, I got what I think might be the start of a workable formula. If, unfortunately, a very long one.

I started off by trying to change the value of cell B110 from a manual summation of the value I was looking for (the sum of all voters who prefer the candidate named in cells B2 and B95 over the candidate named in cell A110) to a formulaic one. The start of the formula is below.

=sumif($B$95:$B$105,if(match(B$95,$B$95:$B$105,0)<match(regexextract($A110,"vss([A-z|ä]+)"),$B$95:$B$105,0)=true,B$95,0),B106:L106)

It produced the following table. The only change from the second markdown table is that the cell right next to “vs Finwio” now only has the votes from the voters whose first preference is Melian:

The match formulas return the position of the cell in question, proxying for the preference of the voters who support the candidate named at the top of the column. If the candidate’s cell position is lower than that of their opponent, then the criterion for the sumif is the candidate’s name. Otherwise, it returns 0.

This only applies to one column out of eleven, of course. I’d need to copy and modify this eleven times for each of the eleven columns, representing the voters for each of the other ten candidates. In the end, the formula would be a

sum(sumif(the formula above),sumif(the formula above, but for column C), sumif (the formula above, for column D)

…for each column from B to L.

So…what I want is to find out if there exists a Google Sheets formula that can:

• find a way to put all of the columns and rows in the same array
• find and compare the position of two strings in multiple rows
• sum up all of the votes (here, the numbers in row 106) in each column where the string representing the candidate listed in the column has a lower row than their opponent
• provides the summed-up value in one cell
• performs the above in a more compact manner than summing up sumifs

And if so, I’d appreciate some help in building it. I can share a copy of the sheet I did all of the work on originally, if needed.

This is the sample Google Sheet I’ve made with the relevant data and my one-cell attempt at a formula change.