Excel 2003 Finding most common text value

[delane]

I am trying to find a formula that will calculate the most common finding the three most common job numbers used so that I can spread a discount between them. I use Excel 2003. I am sure this is very easy and I am simply having a brain fart, but if anyone could help I would greatly appreciate it. I know the file uploaded is simple, but some months is is very long. I would like to set the formulas up in the template so I don't have to do this each month.

Thanks

[newdoverman]

This will count the non blank cells in column A.

Please Login or Register  to view this content.

[benishiryo]

welcome to the forum, delane. i provided 3 methods in the file. since you mentioned you have lots of records, i suggest you do the pivot, filter by top 3 & sort them. the other 2 methods are array formulas & will slow down the file if there are lots of records

the 2nd method is to just find the Job Names:

Please Login or Register  to view this content.

the 3rd method is to find the Job Names & Count:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

both of them are array formulas...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

all of them show 4 results because there is a tie for 3rd & 4th.

[delane]

thanks for all the help. I really appreciate it.: