Count top 7 list of names in a sum product formula

[Shamz41]

Hi

I have a column H, which has a list of names. The below formula can count an added criteria for a month that is indicated in cell AD2 up to the whole year. The dates are located in column U. In this below formula one of the criteria is in column P which is to exclude the word cancelled.
Can i know how do i add in to this formula to count the top 7 list of names in column H?

Please Login or Register  to view this content.

[PaulM100]

Try: =SUMPRODUCT((Report!$P$4:$P$1000<>"Cancelled")*YEAR(Report!$U:$U)=YEAR($AD$2))*(MONTH(Report!$U:$U)=MONTH($AD$2))*(Large(your column,{1,2,3,4,5,6,7})))

[Shamz41]

Hi Paul.

Thank you for looking into it. I seemed to have gotten a value error. I have attached a sample workbook with expected results.

[PaulM100]

How do you know which ones are the top 7 as you don't have any numbers in the file?

[Shamz41]

Hi Paul. Can the formula be adapted to count the number of times the name appears in column H and do a classification of top 7 names form there?

[Glenn Kennedy]

Your explanation is a bit vague!! How can Mic be top of the list when he has only 1 valid result??

An array formula:

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[kvsrinivasamurthy]

In O16 then copy down

Please Login or Register  to view this content.

[Glenn Kennedy]

Hahaha. I didn't see AD1....