I need to know the top 3 items. I've found the formula for the most common item, and how to count the number of that item, but not how to find the second and third most common item.

=INDEX(Report!A1:A57,MODE(MATCH(Report!A1:A57,Repo rt!A1:A57,0)))
=COUNTIF(Report!A:A, "*"&E4&"*")


Attached is a mock worksheet. One issue is the INDEX formula doesn't work if a blank cell is selected and the total number of items changes day to day. The most common items change day to day as well. I keep thinking there's a =LARGE formula that can be added or something, but I can't find an example to work off of.

Attached Files

MsForums.xlsx (14.3 KB, 7 views)

Why make this so difficult?

A list of all goods/fruits has an additional bonus, as you can use data validation to ensure no typos mess up with your data.

(I leave it to you to figure out what to do when there are several goods with same count - currently Bananas and Oranges must share order numbers 6 and 7, instead 7 they both have initially. What about when you have such a pair/trio/whatever somewhere at top 3 positions?)

Attached Files

CountGoods.xlsx (17.4 KB, 6 views)

Pivot table at cell F8 of the Main sheet.
The Names column is

1. filtered by Top Ten (top 3 in this case) based on the Count of Status
2. Sorted descending based on ther Count of Status.

Attached Files

msofficeforums46375MsForums.xlsx (18.0 KB, 5 views)