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]

View attachment 14716

Ctrl shift enter fort the array formula, not just Enter.

 

[danthesuperman]

Ctrl shift enter fort the array formula, not just Enter.

Awesome!

Now I am just having issues transposing this over to my actual sheet where I use an index to find the department eg CI_XXXXX and thus use "*CI&" and the data is on a seperate tab.

I'm currently looking like this:
=(COUNTIFS('Faculty Merge List'!$C$28:INDEX('Faculty Merge List'!$C$28:$C$61,SMALL('Faculty Merge List'!$F$28:$F$61="Yes",ROW('Faculty Merge List'!$F$28:$F$61)),7),"*CI*",'Faculty Merge List'!$F$2:INDEX('Faculty Merge List'!$F$28:$F$61,SMALL(IF('Faculty Merge List'!$F$28:$F$61="Yes",ROW('Faculty Merge List'!$F$28:$F$61)),7)),"Yes"))

But it just comes up like this:

 

[shaowu459]

=COUNTIFS('Faculty Merge List'!$C$28:INDEX('Faculty Merge List'!$C:$C,SMALL('Faculty Merge List'!$F$28:$F$61="Yes",ROW('Faculty Merge List'!$F$28:$F$61)),7),"*CI*",'Faculty Merge List'!$F$2: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")


Remember Ctrl shift enter

 

[danthesuperman]

=COUNTIFS('Faculty Merge List'!$C$28:INDEX('Faculty Merge List'!$C:$C,SMALL('Faculty Merge List'!$F$28:$F$61="Yes",ROW('Faculty Merge List'!$F$28:$F$61)),7),"*CI*",'Faculty Merge List'!$F$2: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")


Remember Ctrl shift enter

Still getting the #NUM! error .

 

[shaowu459]

Still getting the #NUM! error .
View attachment 14719

You lost the IF after SMALL, please refer to my original formula. i am using phone, so you may modify the formula by yourself.

 

[danthesuperman]

You lost the IF after SMALL, please refer to my original formula. i am using phone, so you may modify the formula by yourself.

Still getting a VALUE error after using this:

=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)),"*CI*",'Faculty Merge List'!$F$2: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")

 

[shaowu459]

Still getting a VALUE error after using this:

=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)),"*CI*",'Faculty Merge List'!$F$2: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")

please upload a screenshot of current error.

 

[danthesuperman]

please upload a screenshot of current error.

 

[shaowu459]

View attachment 14730

after check the formula again and again.....change F2 to F28

=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)),"*CI*",'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")

 

[danthesuperman]

after check the formula again and again.....change F2 to F28

=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)),"*CI*",'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")

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!!!!!!