0

I need one solution to the below scenario.

Lets say I have a table of data in four column. I want to know the maximum row in excel sheet. For eg: As you can see in the below table column B has maximum number of rows which is equal to 12.

    A   B   C   D
1   asd asd asd asd
2   asd asd asd asd
3   asd asd asd asd
4   asd asd asd asd
5   asd asd asd asd
6       asd     asd
7       asd     asd
8       asd
9       asd
10      asd
11      asd
12      asd

My solution is to individually check each column to get the maximum number of rows. Is there any other optimize solution to this??

Community
  • 1
  • 1
Paresh J
  • 2,401
  • 3
  • 24
  • 31
  • @Chris: The question asked in the linked post is different than what i have asked. No doubt it has solution to my Answer but question is different. Question in that post is looking for last row in used range and I am not looking for Last used range. – Paresh J Oct 25 '14 at 07:02
  • and how is that different? – chris neilsen Oct 25 '14 at 07:11
  • Count of rows return by used range and actual number of rows can be different. And that particular post asked for Last used cell or range. And I was asking for actual rows in sheet. – Paresh J Oct 25 '14 at 07:16
  • Look at the accepted answer by Sid, in particular the method using `Find` – chris neilsen Oct 25 '14 at 07:17
  • Yes, Answer applies to me too. And Yes, my question was intended to find the actual rows. – Paresh J Oct 25 '14 at 07:23

1 Answers1

0

You can simply use CountIf(range, "<>"&"").

More options: https://www.ablebits.com/office-addins-blog/2014/07/02/excel-countif-examples/

After that use Max(range) to get the maximum number.

ClearBoth
  • 2,235
  • 2
  • 18
  • 24
  • How can we use countif? Data is not same in any column. What I have used is sample text. Your formula will get me all non blank cells for each column right?? – Paresh J Oct 25 '14 at 06:45