Openoffice Calc (or Excel) count matches in other cell in column

Question

I've searched to no avail, and my spreadsheet skills are simply not sufficient to tackle this.

Say I have 5 rows of numbers in 90 columns. For each column I want to find the maximum number. That is easy enough, and I can create an auxiliary row for this. However, what I want is to count how many columns each row contains the maximum number for.

In a dataset like the following:

a b c d 
1 2 3 4 
5 4 3 2 
4 4 4 4 
1 1 1 1

I want the following:

 a b c d   countmax
 1 2 3 4   1
 5 4 3 2   2
 4 4 4 4   3
 1 1 1 1   0

With the auxiliary row would, it would look like this:

 a b c d   countmax
 1 2 3 4   1
 5 4 3 2   2
 4 4 4 4   3
 1 1 1 1   0
 5 4 4 4

I've looked into sumproduct, countif, hlookup, vlookup, and others, but I cannot figure out how to do this. I have currently worked around it by adding an extra row for each row, and doing a comparison on each cell, then counting the boolean results. ( cell content: "=IF(a1 = a5;1;0)" content in the cell for the final column: "=COUNTIF(a6:d6;1)" )

Ideally, the solution is a formula that does not depend on any auxiliary cells, and just goes column by column, counting how many times the cell in that row and column is maximum value. I would also be curious to know how to do it with the single auxiliary row, since the approach would require a cell comparison.

Thanks in advance.

Answer 1

To do it without an auxiliary row, you can use Offset to get each column in turn, then Subtotal to get the maximum of each column:

=SUMPRODUCT(--(A1:D1=SUBTOTAL(4,OFFSET($A$1,0,COLUMN($A$1:$D$4)-COLUMN($A$1),ROWS($A$1:$D$4)))))

This should be fine normally, but Offset is a volatile function and may cause performance issues with large sheets. Also, this combination of Offset and Subtotal is peculiar to Excel and doesn't work in Open Office or Google Sheets.

Answer 2

"I would also be curious to know how to do it with the single auxiliary row."

With the formula:

=SUMPRODUCT(--(A1:D1=$A$5:$D$5))