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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]max[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]max count[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]row(s)[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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