Is there a function to count (only the) rows where the values in column `A` are greater than corresponding value in column `B`?

Question

Scores in a STAR Score voting election:

	A	B	C	D	E	F	G
1	Candidate A	Candidate B	Candidate C	Is A > B?	Is A > C?	Is B > C?	Is C > B?
2	5	3	2	T	T	T	F
3	0	4	3	F	F	T	F
4	2	3	5	F	F	F	T
5	3	4	4	T	F	F	F
6			Tally	2	1	2	1
7			%	50%	25%	50%	25%
8	Score A = 10	Score B = 14	Score C = 14

In practice I will have 5 or more Candidates and many rows of data (STAR votes). This is a small example, I can create a column, like Columns D, E, F & G to do this for each comparison I need to make and tally (eg how many times in this range of data is the cell in column A greater than corresponding cell in column B?) but with a larger dataset I might end up with (n-1)! columns where n is the number of 'Candidates'. I want to compact it into a single formula in a single cell D6, and then copy it to E6, F6 & G6 if this possible without VBA or whatever.

So I'm looking for a function that would CountIf for each row in the range if the value in a specified column referenced is greater than some other referenced column in the range referenced (or first and last columns to avoid using arguments to locate the columns).

I guess a SumIF or CountIF type of function but with more flexible arguments, i.e. without a hard coded comparison argument like ">" 4, but a reference to the data in each row itself, so the function would tabulate column D and then count the True values for me.

hard to explain but I come across this kind of problem from time to time… I guess I could do in Excel with Lambda functions but I haven't learnt how to do them in Excel yet.

Answer 1

The Excel Spreadsheet linked to avoids making multiple array calculations in a single cell and uses the fact that in STAR voting the highest and second highest scorers are taken to a run-off where preferences are counted. Provided more for explanation of the concepts than answering the OP.

That means this preference comparison only needs to take place between two candidates, not every combination of two candidates. Takes a lot of the pain out of it.

This spreadsheet mostly works, doesn't seem to like zero scores for some reason… gives N/A in the IF A > B formulas. It has some examples of how different results are produced in different circumstances.

[Excel Spreadsheet link.]

Answer 2

=ArrayFormula({A2:A5>B2:B5, A2:A5>C2:C5, B2:B5>C2:C5, C2:C5>B2:B5})

=ArrayFormula(TRANSPOSE(MMULT(TRANSPOSE({A2:A5>B2:B5, A2:A5>C2:C5, B2:B5>C2:C5, C2:C5>B2:B5}*1), SEQUENCE(4, 1, 1, 0))))

=ArrayFormula((1/SUM(
 TRANSPOSE(MMULT(TRANSPOSE({A2:A5>B2:B5, A2:A5>C2:C5, B2:B5>C2:C5, C2:C5>B2:B5}*1), SEQUENCE(4, 1, 1, 0)))))*
 TRANSPOSE(MMULT(TRANSPOSE({A2:A5>B2:B5, A2:A5>C2:C5, B2:B5>C2:C5, C2:C5>B2:B5}*1), SEQUENCE(4, 1, 1, 0))))