COUNTIFS up to a certain number of occurences

[danthesuperman]

I am using a COUNTIFS forumla to count the number of times a row matches two criterea. This is to act as a kind of dynamic counter for a scholarship ranking sheet to show how many scholarship are allocated to students in each department as we move the ranking around. However I only have 7 of a certain type of scholarship to awarded. Student's eligibility for this scholarship is indicated by the first criteria.

I want the forumla to count only the first seven occurrences of the first criteria.

Currently my forumla looks like this:

=COUNTIFS('Schol List'!$F$28:$F$61,"*YES*",'Schol List'!$C$28:$C$61,"*Department A*")

This formula is then repeated for each department (see image).

What do I need to add so that I only count the first seven occurences of 'Schol List'!$F$28:$F$61,"*YES*" regardless of department?

 

[shaowu459]

Amazing Amazing Amazing!

It works fine!

Also easily transposes for into my Google Sheet as this:

=ArrayFormula(COUNTIFS('Faculty Merge List'!$C$28:INDEX('Faculty Merge List'!$C:$C,SMALL(IF('Faculty Merge List'!$F$28:$F$61="Yes",ROW('Faculty Merge List'!$F$28:$F$61)),7)),"*CH*",'Faculty Merge List'!$F$28:INDEX('Faculty Merge List'!$F:$F,SMALL(IF('Faculty Merge List'!$F$28:$F$61="Yes",ROW('Faculty Merge List'!$F$28:$F$61)),7)),"Yes"))

Thanks!!!!!!

Ha~Thanks for your feedback and I'm happy to hear that you finally solved your problem~