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?

 

[Eric W]

Welcome to the forum!

How about:

Book1.xlsx
	A	B	C	D	E	F
1		Special Scholarship
2	Department A	1
3	Department B	3
4	Department C	1
5	Department D	2
6	Total	7
7
27
28			Department A			Yes
29			Department B			Yes
30			Department B			Yes
31			xx			Yes
32			Department C			Yes
33			Department D			Yes
34			Department B			Yes
35			Department D			Yes
36			Department D			Yes
37			Department D			Yes
Schol List

Cell Formulas
Range	Formula
B2:B5	B2	=MIN(COUNTIFS('Schol List'!$F$28:$F$61,"*YES*",'Schol List'!$C$28:$C$61,A2),7-SUM(B$1:B1))
B6	B6	=SUM(B2:B5)

 

[danthesuperman]

Welcome to the forum!

How about:

Book1.xlsx
	A	B	C	D	E	F
1		Special Scholarship
2	Department A	1
3	Department B	3
4	Department C	1
5	Department D	2
6	Total	7
7
27
28			Department A			Yes
29			Department B			Yes
30			Department B			Yes
31			xx			Yes
32			Department C			Yes
33			Department D			Yes
34			Department B			Yes
35			Department D			Yes
36			Department D			Yes
37			Department D			Yes
Schol List

Cell Formulas
Range	Formula
B2:B5	B2	=MIN(COUNTIFS('Schol List'!$F$28:$F$61,"*YES*",'Schol List'!$C$28:$C$61,A2),7-SUM(B$1:B1))
B6	B6	=SUM(B2:B5)

Hi Eric,

Thanks. I can't seem to get this to work for me.

Maybe if I share a something more similar to what I am actually working on:

Rank	Department A	Applicant Summary	Special Scholarship
1​	Department A	XXX	Yes
2​	Department B	XXX
3​	Department A	XXX	Yes
4​	Department C	XXX
5​	Department A	XXX	Yes
6​	Department D	XXX
7​	Department C	XXX
8​	Department C	XXX	Yes
9​	Department A	XXX
10​	Department B	XXX
11​	Department D	XXX	Yes
12​	Department A	XXX	Yes
13​	Department C	XXX	Yes		Department	Special Scholarship
14​	Department A	XXX			Department A
15​	Department A	XXX	Yes		Department B
16​	Department B	XXX			Department C
17​	Department B	XXX			Department D
18​	Department A	XXX
19​	Department D	XXX	Yes
20​	Department D	XXX	Yes
21​	Department C	XXX
22​	Department A	XXX	Yes
23​	Department B	XXX	Yes

I want the Table on the right to count the those eligible for the special scholarship up to seven. Does this make sense?

 

[shaowu459]

Book1.xlsx
	A	B	C	D	E	F	G
1	Rank	Department A	Applicant Summary	Special Scholarship		Department	Special Scholarship
2	1	Department A	XXX	Yes		Department A	4
3	2	Department B	XXX			Department B	0
4	3	Department A	XXX	Yes		Department C	2
5	4	Department C	XXX			Department D	1
6	5	Department A	XXX	Yes			7
7	6	Department D	XXX
8	7	Department C	XXX
9	8	Department C	XXX	Yes
10	9	Department A	XXX
11	10	Department B	XXX
12	11	Department D	XXX	Yes
13	12	Department A	XXX	Yes
14	13	Department C	XXX	Yes
15	14	Department A	XXX
16	15	Department A	XXX	Yes
17	16	Department B	XXX
18	17	Department B	XXX
19	18	Department A	XXX
20	19	Department D	XXX	Yes
21	20	Department D	XXX	Yes
22	21	Department C	XXX
23	22	Department A	XXX	Yes
24	23	Department B	XXX	Yes
Sheet9

Cell Formulas
Range	Formula
G2:G5	G2	=COUNTIFS(B$2:INDEX(B:B,SMALL(IF(D:D="Yes",ROW(D:D)),7)),F2,D$2:INDEX(D:D,SMALL(IF(D:D="Yes",ROW(D:D)),7)),"Yes")
G6	G6	=SUM(G2:G5)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shaowu459]

I understand what you want is:
counting from D2 untile the seventh Yes appears, for example, D2:D14 contains 7 Yes, then count the number of each department which meet two criterias: B:B=department name and D2:D14=Yes. if my understanding is right, my post #4 is one of the solutions.

 

[danthesuperman]

Book1.xlsx
	A	B	C	D	E	F	G
1	Rank	Department A	Applicant Summary	Special Scholarship		Department	Special Scholarship
2	1	Department A	XXX	Yes		Department A	4
3	2	Department B	XXX			Department B	0
4	3	Department A	XXX	Yes		Department C	2
5	4	Department C	XXX			Department D	1
6	5	Department A	XXX	Yes			7
7	6	Department D	XXX
8	7	Department C	XXX
9	8	Department C	XXX	Yes
10	9	Department A	XXX
11	10	Department B	XXX
12	11	Department D	XXX	Yes
13	12	Department A	XXX	Yes
14	13	Department C	XXX	Yes
15	14	Department A	XXX
16	15	Department A	XXX	Yes
17	16	Department B	XXX
18	17	Department B	XXX
19	18	Department A	XXX
20	19	Department D	XXX	Yes
21	20	Department D	XXX	Yes
22	21	Department C	XXX
23	22	Department A	XXX	Yes
24	23	Department B	XXX	Yes
Sheet9

Cell Formulas
Range	Formula
G2:G5	G2	=COUNTIFS(B$2:INDEX(B:B,SMALL(IF(D:D="Yes",ROW(D:D)),7)),F2,D$2:INDEX(D:D,SMALL(IF(D:D="Yes",ROW(D:D)),7)),"Yes")
G6	G6	=SUM(G2:G5)
Press CTRL+SHIFT+ENTER to enter array formulas.

This looks like it is doing exactly what I want but I'm getting a #NUM! error. Even when I am copying your entire spreadsheet.

Apologies if this is a bit of a basic question.

 

[Eric W]

I think after looking at your latest example, and shaowu459's formula, I think he has the right idea. Here's another formula which does the same thing but doesn't require array entry:

G2:G5: =SUMPRODUCT(--(B$2:B$24=F2),--(D$2:D$24="yes"),--(ROW(B$2:B$24)<=AGGREGATE(15,6,ROW(D$2:D$24)/(D$2:D$24="yes"),7)))

 

[shaowu459]

This looks like it is doing exactly what I want but I'm getting a #NUM! error. Even when I am copying your entire spreadsheet.

Apologies if this is a bit of a basic question.

will you please upload a screenshot to show the error? make the editing bar visible so I can see the formula.

 

[danthesuperman]

I think after looking at your latest example, and shaowu459's formula, I think he has the right idea. Here's another formula which does the same thing but doesn't require array entry:

G2:G5: =SUMPRODUCT(--(B$2:B$24=F2),--(D$2:D$24="yes"),--(ROW(B$2:B$24)<=AGGREGATE(15,6,ROW(D$2:D$24)/(D$2:D$24="yes"),7)))

Thanks this works perfectly.

Only trouble is I am actually working on a sheet that doesn't have 'Department A' listed against each Row. Instead I have a complicatd index which is "Department_ID Number" eg Department A would be "DA_12345678" and I can't seem to work out how to do the partial string search that I had been using - eg "*DA*" in previous forumlas. Wish I could share my sheet but too much sensitive data.

I was also hoping to simply put this into Google Sheets but of course it doesnt have the Aggregate formula in Sheets - one of the many issues with organisations encouraging staff to use Google Programs.

 

[danthesuperman]

Book1.xlsx
	A	B	C	D	E	F	G
1	Rank	Department A	Applicant Summary	Special Scholarship		Department	Special Scholarship
2	1	Department A	XXX	Yes		Department A	4
3	2	Department B	XXX			Department B	0
4	3	Department A	XXX	Yes		Department C	2
5	4	Department C	XXX			Department D	1
6	5	Department A	XXX	Yes			7
7	6	Department D	XXX
8	7	Department C	XXX
9	8	Department C	XXX	Yes
10	9	Department A	XXX
11	10	Department B	XXX
12	11	Department D	XXX	Yes
13	12	Department A	XXX	Yes
14	13	Department C	XXX	Yes
15	14	Department A	XXX
16	15	Department A	XXX	Yes
17	16	Department B	XXX
18	17	Department B	XXX
19	18	Department A	XXX
20	19	Department D	XXX	Yes
21	20	Department D	XXX	Yes
22	21	Department C	XXX
23	22	Department A	XXX	Yes
24	23	Department B	XXX	Yes
Sheet9

Cell Formulas
Range	Formula
G2:G5	G2	=COUNTIFS(B$2:INDEX(B:B,SMALL(IF(D:D="Yes",ROW(D:D)),7)),F2,D$2:INDEX(D:D,SMALL(IF(D:D="Yes",ROW(D:D)),7)),"Yes")
G6	G6	=SUM(G2:G5)
Press CTRL+SHIFT+ENTER to enter array formulas.