Individually Count Multiple Rows

[bkalma]

I am trying to write an array formula that will individually count the number of values in a set of rows and return the maximum count. For example, I would like the formula to return a value of 4 for the following table as the maximum number of values in a single row is 4 (row 3):

1	1		1
1	1
1	1	1		1
			1
1		1		1

<tbody>
</tbody>

Thanks!

 

[Eric W]

Welcome to the forum.

If your range is A1:E5, try this:

=MAX(SUBTOTAL(2,OFFSET($A$1:$E$1,ROW($A$1:$E$5)-1,0)))

confirmed with Control+Shift+Enter, not just enter.

Note that this formula will only count numeric values, and ignore text values. If you want to count text values as well, change the 2 to a 3.

Let me know if this helps.

 

[bkalma]

Thanks Eric, the formula works, but I forgot to mention that the actual range for my data is D23:AC46. How should I modify the formula to work for any range?

bkalma.

 

[bkalma]

I manged to get it working using the following (Entered as an array formula):

=MAX(SUBTOTAL(2,OFFSET($D$23:$AC$23,ROW($D$23:$AC$46)-ROW($D$23:$AC$23),0)))

Thanks again for the help.

bkalma.

 

[bkalma]

Would there also be a way to write another formula which returns the number of the row with maximum count?

bkalma.

 

[Aladin Akyurek]

I manged to get it working using the following (Entered as an array formula):

=MAX(SUBTOTAL(2,OFFSET($D$23:$AC$23,ROW($D$23:$AC$46)-ROW($D$23:$AC$23),0)))

Thanks again for the help.

bkalma.

Control+shift+enter, not just enter:

=MAX(MMULT((D23:AC46=1)+0,TRANSPOSE(COLUMN(D23:AC46)^0)))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MAX(MMULT((D23:AC46=1)+0,TRANSPOSE(COLUMN(D23:AC46)^0)))

Would there also be a way to write another formula which returns the number of the row with maximum count?

bkalma.

Row\Col	D​
1​	max
2​	4​
3​	max count
4​	1​
5​	row(s)
6​	25​
7​
8​

<tbody>
</tbody>

In D2 control+shift+enter, not just enter:

=MAX(MMULT((D23:AC46=1)+0,TRANSPOSE(COLUMN(D23:AC46)^0)))

In D4, control+shift+enter:

=SUM(IF(MMULT((D23:AC46=1)+0,TRANSPOSE(COLUMN(D23:AC46)^0))=D2,1))

In D6 control+shift+enter and copy down:

=IF(ROWS($D$6:D6)<=$D$4,SMALL(IF(MMULT(($D$23:$AC$46=1)+0,TRANSPOSE(COLUMN($D$23:$AC$46)^0))=$D$2,ROW($D$23:$D$46)),ROWS($D$6:D6)),"")

The latter yields the native row numbers on which the most number of 1's occur.

If it's any number, not just 1, change in the formulas the occurrences of:

(D23:AC46=1)+0

to:

ISNUMBER(D23:AC46)+0