COUNTIFS for 2 criteria

[VPLUMWOOD]

I am trying to count how many times something appears in a range of multiple columns,

So the below is counting how many times the criteria in SummaryAll U2 appears - which is 8 and correct, but I need it to also count if criteria in SummaryAll E2 also appears?

Is this doable?


=COUNTIFS('Data From Arbor'!$K$2:$K$5000,'Summary All'!U2,'Data From Arbor'!$AG$2:$AG$5000,'Summary All'!U2,'Data From Arbor'!$AM$2:$AM$5000,'Summary All'!U2,'Data From Arbor'!$AO$2:$AO$5000,'Summary All'!U2,'Data From Arbor'!$M$2:$M$5000,'Summary All'!U2,'Data From Arbor'!$AI$2:$AI$5000,'Summary All'!U2,'Data From Arbor'!$AA$2:$AA$5000,'Summary All'!U2,'Data From Arbor'!$AC$2:$AC$5000,'Summary All'!U2,'Data From Arbor'!$AK$2:$AK$5000,'Summary All'!U2,'Data From Arbor'!$O$2:$O$5000,'Summary All'!U2,'Data From Arbor'!$Q$2:$Q$5000,'Summary All'!U2,'Data From Arbor'!$S$2:$S$5000,'Summary All'!U2)

 

[Kaper]

So at the moment you are counting how many rows in 2 and 5000 you had value from 'Summary All'!U2 in all 12 columns of a given row.
If you want to count how may times 'Summary All'!E2 appears use the same type formula just find and replace U2 with E2.
If you want both whole rows with U2 or whole rows with E2 just add these two formulas.

But probably you look for rows where there could be a mix of U2 and E2 values.
If so, you can use a traditional approach with SUMPRODUCT function.
For 3 first columns K, AG and AM it will be:

=SUMPRODUCT(('Data From Arbor'!$K$2:$K$5000='Summary All'!E2)+('Data From Arbor'!$K$2:$K$5000='Summary All'!U2),('Data From Arbor'!$AG$2:$AG$5000='Summary All'!E2)+('Data From Arbor'!$AG$2:$AG$5000='Summary All'!U2),('Data From Arbor'!$AM$2:$AM$5000='Summary All'!E2)+('Data From Arbor'!$AM$2:$AM$5000='Summary All'!U2))

as you can see for every column there is a sum of 2 parts one comparying with E2 and the second with U2. The result of each comparition is a boolean value (true/false), but if they are added, excel does a typecasting to integer 1/0 values.

As you have version 365 probably also solution with new functions, like FILTER can be provided.
May be it will be shorter one.
Anyway - the above approach works (also with old versions of excel).

For data presented below, And U2 = 1 and E2 = 2, the result will be 5 (two rows with only 1s, two rows with just 2s and one mixed one).

 

[VPLUMWOOD]

Thank you so much,

That is what I am trying to achieve,

To throw another query in, can i use the same formula to then add in numbers for boys or girls,

So on the Data from Arbor sheet, column C says whether they are Boy or Girl, so I need to know the numbers of males who hit the criteria,

39 is the number of the whole group who matched the 2 criteria, but I need to also know of the 20 Girls, how many of them did......

Year Group Total	45​	39​	87%​
Girls	20​
Boys	25​

=SUMPRODUCT(('Data From Arbor'!$K$2:$K$5000='Summary All'!E2)+('Data From Arbor'!$K$2:$K$5000='Summary All'!U2),('Data From Arbor'!$AG$2:$AG$5000='Summary All'!E2)+('Data From Arbor'!$AG$2:$AG$5000='Summary All'!U2),('Data From Arbor'!$AM$2:$AM$5000='Summary All'!E2)+('Data From Arbor'!$AM$2:$AM$5000='Summary All'!U2),('Data From Arbor'!$AO$2:$AO$5000='Summary All'!E2)+('Data From Arbor'!$AO$2:$AO$5000='Summary All'!U2),('Data From Arbor'!$M$2:$M$5000='Summary All'!E2)+('Data From Arbor'!$M$2:$M$5000='Summary All'!U2),('Data From Arbor'!$AI$2:$AI$5000='Summary All'!E2)+('Data From Arbor'!$AI$2:$AI$5000='Summary All'!U2),('Data From Arbor'!$AA$2:$AA$5000='Summary All'!E2)+('Data From Arbor'!$AA$2:$AA$5000='Summary All'!U2),('Data From Arbor'!$AC$2:$AC$5000='Summary All'!E2)+('Data From Arbor'!$AC$2:$AC$5000='Summary All'!U2),('Data From Arbor'!$AK$2:$AK$5000='Summary All'!E2)+('Data From Arbor'!$AK$2:$AK$5000='Summary All'!U2),('Data From Arbor'!$O$2:$O$5000='Summary All'!E2)+('Data From Arbor'!$O$2:$O$5000='Summary All'!U2),('Data From Arbor'!$S$2:$S$5000='Summary All'!E2)+('Data From Arbor'!$S$2:$S$5000='Summary All'!U2),('Data From Arbor'!$Q$2:$Q$5000='Summary All'!E2)+('Data From Arbor'!$Q$2:$Q$5000='Summary All'!U2))

 

[myall_blues]

Here's a 365 formula for the first problem Maybe not a lot better but perhaps a little more readable? There may well be a more compact solution but can't say without seeing an example of your data.

=LET(crit1,'Summary All'!E2,
crit2,'Summary All'!U2,
k_range,'Data from Arbor'!K2:K5000,
m_range,'Data from Arbor'!M2:M5000,
o_range,'Data from Arbor'!O2:O5000,
q_range,'Data from Arbor'!Q2:Q5000,
s_range,'Data from Arbor'!S2:S5000,
aa_range,'Data from Arbor'!AA2:AA5000,
aC_range,'Data from Arbor'!AC2:AC5000,
ag_range,'Data from Arbor'!AG2:AG5000,
ai_range,'Data from Arbor'!AI2:AI5000,
ak_range,'Data from Arbor'!AK2:AK5000,
am_range,'Data from Arbor'!AM2:AM5000,
COUNT(FILTER(k_range,((k_range=crit1)+(k_range=crit2))*((m_range=crit1)+(m_range=crit2))*((o_range=crit1)+(o_range=crit2))*((q_range=crit1)+(q_range=crit2))*((s_range=crit1)+(s_range=crit2))*((aa_range=crit1)+(aa_range=crit2))*((aC_range=crit1)+(aC_range=crit2))*((ag_range=crit1)+(ag_range=crit2))*((ai_range=crit1)+(ai_range=crit2))*((ak_range=crit1)+(ak_range=crit2))*((am_range=crit1)+(am_range=crit2)))))

And here's one for the second problem. Assumes you have the word boy or girl in 'Summary All'!C2 and also in 'Data from Arbor'!C2:C5000

=LET(crit1,'Summary All'!E2,
crit2,'Summary All'!U2,
crit3,'Summary All'!C2,
c_range,'Data from Arbor'!C2:C5000,
k_range,'Data from Arbor'!K2:K5000,
m_range,'Data from Arbor'!M2:M5000,
o_range,'Data from Arbor'!O2:O5000,
q_range,'Data from Arbor'!Q2:Q5000,
s_range,'Data from Arbor'!S2:S5000,
aa_range,'Data from Arbor'!AA2:AA5000,
aC_range,'Data from Arbor'!AC2:AC5000,
ag_range,'Data from Arbor'!AG2:AG5000,
ai_range,'Data from Arbor'!AI2:AI5000,
ak_range,'Data from Arbor'!AK2:AK5000,
am_range,'Data from Arbor'!AM2:AM5000,
COUNT(FILTER(k_range,(c_range=crit3)*((k_range=crit1)+(k_range=crit2))*((m_range=crit1)+(m_range=crit2))*((o_range=crit1)+(o_range=crit2))*((q_range=crit1)+(q_range=crit2))*((s_range=crit1)+(s_range=crit2))*((aa_range=crit1)+(aa_range=crit2))*((aC_range=crit1)+(aC_range=crit2))*((ag_range=crit1)+(ag_range=crit2))*((ai_range=crit1)+(ai_range=crit2))*((ak_range=crit1)+(ak_range=crit2))*((am_range=crit1)+(am_range=crit2)))))

 

[VPLUMWOOD]

Hi sorry, that didnt work for the first problem or the 2nd,

Thank you

 

[myall_blues]

Got any more information than "it didn’t work"? I can’t see your data but it worked on a test set, so:
Did you get an error message?
Did you get something like #VALUE in the cell?
Did it work but give an incorrect answer?

 

[VPLUMWOOD]

Got any more information than "it didn’t work"? I can’t see your data but it worked on a test set, so:
Did you get an error message?
Did you get something like #VALUE in the cell?
Did it work but give an incorrect answer?

When I copied this formula in, the value was 0 -

=LET(crit1,'Summary All'!E2, crit2,'Summary All'!U2, k_range,'Data from Arbor'!K2:K5000, m_range,'Data from Arbor'!M2:M5000, o_range,'Data from Arbor'!O2:O5000, q_range,'Data from Arbor'!Q2:Q5000, s_range,'Data from Arbor'!S2:S5000, aa_range,'Data from Arbor'!AA2:AA5000, aC_range,'Data from Arbor'!AC2:AC5000, ag_range,'Data from Arbor'!AG2:AG5000, ai_range,'Data from Arbor'!AI2:AI5000, ak_range,'Data from Arbor'!AK2:AK5000, am_range,'Data from Arbor'!AM2:AM5000, COUNT(FILTER(k_range,((k_range=crit1)+(k_range=crit2))*((m_range=crit1)+(m_range=crit2))*((o_range=crit1)+(o_range=crit2))*((q_range=crit1)+(q_range=crit2))*((s_range=crit1)+(s_range=crit2))*((aa_range=crit1)+(aa_range=crit2))*((aC_range=crit1)+(aC_range=crit2))*((ag_range=crit1)+(ag_range=crit2))*((ai_range=crit1)+(ai_range=crit2))*((ak_range=crit1)+(ak_range=crit2))*((am_range=crit1)+(am_range=crit2)))))

Where as the Sum Product formula returns 39 out of the 45 match the criteria set,

I basically need the sumproduct one but then to somehow work in the countif bit to calculate if column C contains "female" .

This is the screen shot of the data sheet, and then I have a summary all sheet that has formulas to calculate % of children achieving a specific grade.

its the GLD on the summary all sheet bit I am struggling with as children need to achieve a specific grade or higher in 12 of the 16 Areas to make a "Good Level"

 

[myall_blues]

Can you include row and column numbers in both pictures, but second one in particular. Which cell is E2 and U2?

Ideally if you could post a subset of these data (suitably anonymised) using XL2BB then we could derive a solution without having to set it all up from scratch.

Thanks

 

[VPLUMWOOD]

EYFS Data Report 2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1			Baseline			Assessment 1
2	Year Group	FS2		3-4S	4-5E		4-5D	4-5S
3	No of pupils in group		ARE -	ARE	ARE +	ARE -	ARE	ARE +	Not Typical	Typical	Rapid
4
5	Whole School		Being Imaginative and Expressive			Being Imaginative and Expressive
6	Year Group Total	45	2%	98%	0%	23%	77%	28%	18%	82%	5%
7	Girls	20	0%	100%	0%	15%	85%	5%	25%	75%	5%
8	Boys	25	4%	96%	0%	36%	64%	44%	16%	84%	4%
9	SEN	8	0%	100%	0%	63%	38%	25%	0%	100%	0%
10	No SEN	37	3%	97%	0%	19%	81%	27%	24%	76%	5%
11	PP	9	0%	100%	0%	44%	56%	33%	0%	100%	0%
12	No PP	36	3%	97%	0%	22%	78%	25%	25%	75%	6%
13	EAL	17	6%	94%	0%	41%	59%	35%	67%	33%	0%
14	LAC	1	0%	100%	0%	100%	0%	0%	0%	100%	0%
15	Summer Born	14	0%	100%	0%	14%	86%	36%	7%	93%	0%
16			Building Relationships			Building Relationships
17	Year Group Total	45	2%	98%	0%	28%	72%	14%	11%	89%	34%
18	Girls	20	0%	100%	0%	20%	80%	10%	15%	85%	20%
19	Boys	25	4%	96%	0%	40%	60%	16%	12%	88%	44%
20	SEN	8	0%	100%	0%	75%	25%	0%	25%	75%	25%
21	No SEN	37	3%	97%	0%	22%	78%	16%	11%	89%	35%
Summary All

Cell Formulas
Range	Formula
D2	D2	=VLOOKUP($B$2,Expectations!$A$1:$G$3,2,FALSE)
E2	E2	=VLOOKUP($B$2,Expectations!$A$1:$G$3,3,FALSE)
G2	G2	=VLOOKUP($B$2,Expectations!$A$1:$G$3,4,FALSE)
H2	H2	=VLOOKUP($B$2,Expectations!$A$1:$G$3,5,FALSE)
B6,B17	B6	=COUNTIF('Data From Arbor'!$A$2:$A$1048576,"?*")
C6:C15,F6:F15,I6:I15,C17:C21,F17:F21,I17:I21	C6	=100%-D6
D6	D6	=COUNTIF('Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTA('Data From Arbor'!$AE$2:$AE$4993)+E6
E6	E6	=COUNTIF('Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTA('Data From Arbor'!$AE$2:$AE$4993)
G6	G6	=COUNTIF('Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTA('Data From Arbor'!$BM$2:$BM$4993)+H6
H6	H6	=COUNTIF('Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTA('Data From Arbor'!$BM$2:$BM$4993)
J6	J6	=COUNTIF('Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTA('Data From Arbor'!$BN$2:$BN$4993)+K6
K6	K6	=COUNTIF('Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTA('Data From Arbor'!$BN$2:$BN$4993)
B7,B18	B7	=COUNTIFS('Data From Arbor'!$C$2:$C$63,"Female")
D7	D7	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")+E7
E7	E7	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")
G7	G7	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")+H7
H7	H7	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")
J7	J7	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")+K7
K7	K7	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")
B8,B19	B8	=COUNTIFS('Data From Arbor'!$C$2:$C$63,"Male")
D8	D8	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")+E8
E8	E8	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")
G8	G8	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")+H8
H8	H8	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")
J8	J8	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")+K8
K8	K8	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")
B9,B20	B9	=COUNTIF('Data From Arbor'!$F$2:$F$63, "✓")
D9	D9	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")+E9
E9	E9	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")
G9	G9	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")+H9
H9	H9	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")
J9	J9	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")+K9
K9	K9	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")
B10,B21	B10	=COUNTIF('Data From Arbor'!$F$2:$F$63, "✕")
D10	D10	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")+E10
E10	E10	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")
G10	G10	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")+H10
H10	H10	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")
J10	J10	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")+K10
K10	K10	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")
B11	B11	=COUNTIF('Data From Arbor'!$D$2:$D$63, "✓")
D11	D11	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✓")+E11
E11	E11	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✓")
G11	G11	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✓")+H11
H11	H11	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✓")
J11	J11	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✓")+K11
K11,K13	K11	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✓")
B12	B12	=COUNTIF('Data From Arbor'!$D$2:$D$63, "✕")
D12	D12	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✕", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✕")+E12
E12	E12	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✕", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✕")
G12	G12	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✕", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✕")+H12
H12	H12	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✕", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✕")
J12	J12	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✕", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✕")+K12
K12	K12	=COUNTIFS('Data From Arbor'!$D$2:$D$4993,"✕", 'Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTIFS('Data From Arbor'!$D$2:$D$4993, "✕")
B13	B13	=COUNTIF('Data From Arbor'!$G$2:$G$63, "✓")
D13	D13	=COUNTIFS('Data From Arbor'!$G$2:$G$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$G$2:$G$4993, "✓")+E13
E13	E13	=COUNTIFS('Data From Arbor'!$G$2:$G$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$G$2:$G$4993, "✓")
G13	G13	=COUNTIFS('Data From Arbor'!$G$2:$G$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$G$2:$G$4993, "✓")+H13
H13	H13	=COUNTIFS('Data From Arbor'!$G$2:$G$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$G$2:$G$4993, "✓")
J13	J13	=COUNTIFS('Data From Arbor'!$G$2:$G$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$D$2:$G$4993, "✓")+K13
B14	B14	=COUNTIF('Data From Arbor'!$H$2:$H$63, "✓")
D14	D14	=COUNTIFS('Data From Arbor'!$H$2:$H$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$H$2:$H$4993, "✓")+E14
E14	E14	=COUNTIFS('Data From Arbor'!$H$2:$H$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$H$2:$H$4993, "✓")
G14	G14	=COUNTIFS('Data From Arbor'!$H$2:$H$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$H$2:$H$4993, "✓")+H14
H14	H14	=COUNTIFS('Data From Arbor'!$H$2:$H$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$H$2:$H$4993, "✓")
J14	J14	=COUNTIFS('Data From Arbor'!$H$2:$H$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$H$2:$H$4993, "✓")+K14
K14	K14	=COUNTIFS('Data From Arbor'!$H$2:$H$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTIFS('Data From Arbor'!$H$2:$H$4993, "✓")
B15	B15	=COUNTIF('Data From Arbor'!$DG$2:$DG$63, "✓")
D15	D15	=COUNTIFS('Data From Arbor'!$DG$2:$DG$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$D$2)/COUNTIFS('Data From Arbor'!$DG$2:$DG$4993, "✓")+E15
E15	E15	=COUNTIFS('Data From Arbor'!$DG$2:$DG$4993,"✓", 'Data From Arbor'!$AE$2:$AE$4993,$E$2)/COUNTIFS('Data From Arbor'!$DG$2:$DG$4993, "✓")
G15	G15	=COUNTIFS('Data From Arbor'!$DG$2:$DG$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$G$2)/COUNTIFS('Data From Arbor'!$DG$2:$DG$4993, "✓")+H15
H15	H15	=COUNTIFS('Data From Arbor'!$DG$2:$DG$4993,"✓", 'Data From Arbor'!$BM$2:$BM$4993,$E$2)/COUNTIFS('Data From Arbor'!$DG$2:$DG$4993, "✓")
J15	J15	=COUNTIFS('Data From Arbor'!$DG$2:$DG$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$J$3)/COUNTIFS('Data From Arbor'!$DG$2:$DG$4993, "✓")+K15
K15	K15	=COUNTIFS('Data From Arbor'!$DG$2:$DG$4993,"✓", 'Data From Arbor'!$BN$2:$BN$4993,$K$3)/COUNTIFS('Data From Arbor'!$DG$2:$DG$4993, "✓")
D17	D17	=COUNTIF('Data From Arbor'!$AG$2:$AG$4993,$D$2)/COUNTA('Data From Arbor'!$AG$2:$AG$4993)+E17
E17	E17	=COUNTIF('Data From Arbor'!$AG$2:$AG$4993,$E$2)/COUNTA('Data From Arbor'!$AG$2:$AG$4993)
G17	G17	=COUNTIF('Data From Arbor'!$BO$2:$BO$4993,$G$2)/COUNTA('Data From Arbor'!$BO$2:$BO$4993)+H17
H17	H17	=COUNTIF('Data From Arbor'!$BO$2:$BO$4993,$E$2)/COUNTA('Data From Arbor'!$BO$2:$BO$4993)
J17	J17	=COUNTIF('Data From Arbor'!$CV$2:$CV$4993,$J$3)/COUNTA('Data From Arbor'!$CV$2:$CV$4993)+K17
K17	K17	=COUNTIF('Data From Arbor'!$CV$2:$CV$4993,$K$3)/COUNTA('Data From Arbor'!$CV$2:$CV$4993)
D18	D18	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$AG$2:$AG$4993,$D$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")+E18
E18	E18	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$AG$2:$AG$4993,$E$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")
G18	G18	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$BO$2:$BO$4993,$G$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")+H18
H18	H18	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$BO$2:$BO$4993,$E$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")
J18	J18	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$CV$2:$CV$4993,$J$3)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")+K18
K18	K18	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Female", 'Data From Arbor'!$CV$2:$CV$4993,$K$3)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Female")
D19	D19	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$AG$2:$AG$4993,$D$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")+E19
E19	E19	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$AG$2:$AG$4993,$E$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")
G19	G19	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$BO$2:$BO$4993,$G$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")+H19
H19	H19	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$BO$2:$BO$4993,$E$2)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")
J19	J19	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$CV$2:$CV$4993,$J$3)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")+K19
K19	K19	=COUNTIFS('Data From Arbor'!$C$2:$C$4993,"Male", 'Data From Arbor'!$CV$2:$CV$4993,$K$3)/COUNTIFS('Data From Arbor'!$C$2:$C$4993, "Male")
D20	D20	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$AG$2:$AG$4993,$D$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")+E20
E20	E20	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$AG$2:$AG$4993,$E$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")
G20	G20	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$BO$2:$BO$4993,$G$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")+H20
H20	H20	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$BO$2:$BO$4993,$E$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")
J20	J20	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$CV$2:$CV$4993,$J$3)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")+K20
K20	K20	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✓", 'Data From Arbor'!$CV$2:$CV$4993,$K$3)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✓")
D21	D21	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$AG$2:$AG$4993,$D$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")+E21
E21	E21	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$AG$2:$AG$4993,$E$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")
G21	G21	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$BO$2:$BO$4993,$G$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")+H21
H21	H21	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$BO$2:$BO$4993,$E$2)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")
J21	J21	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$CV$2:$CV$4993,$J$3)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")+K21
K21	K21	=COUNTIFS('Data From Arbor'!$F$2:$F$4993,"✕", 'Data From Arbor'!$CV$2:$CV$4993,$K$3)/COUNTIFS('Data From Arbor'!$F$2:$F$4993, "✕")

 

[VPLUMWOOD]

EYFS Data Report 2024.xlsx
	S	T	U	V	W	X	Y	Z
1	GLD
2	Year Group	FS2	3-4S	3-4S	4-5D	4-5D	ELG D	ELG D
3	No of pupils in group		Baseline No	Baseline %	Assess 1 No	Assess 1 %	Assess 2 No	Assess 2 %
4
5	Whole School
6	Year Group Total	45	39	87%	16	36%
7	Girls	20
8	Boys	25
9	SEN	8
10	No SEN	37
11	PP	9
12	No PP	36
13	EAL	17
14	LAC	1
15	Summer Born	14
Summary All

Cell Formulas
Range	Formula
U2:V2	U2	=VLOOKUP($B$2,Expectations!$A$1:$G$3,2,FALSE)
W2:X2	W2	=VLOOKUP($B$2,Expectations!$A$1:$G$3,4,FALSE)
Y2:Z2	Y2	=VLOOKUP($B$2,Expectations!$A$1:$G$3,6,FALSE)
U6	U6	=SUMPRODUCT(('Data From Arbor'!$K$2:$K$5000='Summary All'!$E$2)+('Data From Arbor'!$K$2:$K$5000='Summary All'!$U$2),('Data From Arbor'!$AG$2:$AG$5000='Summary All'!$E$2)+('Data From Arbor'!$AG$2:$AG$5000='Summary All'!$U$2),('Data From Arbor'!$AM$2:$AM$5000='Summary All'!$E$2)+('Data From Arbor'!$AM$2:$AM$5000='Summary All'!$U$2),('Data From Arbor'!$AO$2:$AO$5000='Summary All'!$E$2)+('Data From Arbor'!$AO$2:$AO$5000='Summary All'!$U$2),('Data From Arbor'!$M$2:$M$5000='Summary All'!$E$2)+('Data From Arbor'!$M$2:$M$5000='Summary All'!$U$2),('Data From Arbor'!$AI$2:$AI$5000='Summary All'!$E$2)+('Data From Arbor'!$AI$2:$AI$5000='Summary All'!$U$2),('Data From Arbor'!$AA$2:$AA$5000='Summary All'!$E$2)+('Data From Arbor'!$AA$2:$AA$5000='Summary All'!$U$2),('Data From Arbor'!$AC$2:$AC$5000='Summary All'!$E$2)+('Data From Arbor'!$AC$2:$AC$5000='Summary All'!$U$2),('Data From Arbor'!$AK$2:$AK$5000='Summary All'!$E$2)+('Data From Arbor'!$AK$2:$AK$5000='Summary All'!$U$2),('Data From Arbor'!$O$2:$O$5000='Summary All'!$E$2)+('Data From Arbor'!$O$2:$O$5000='Summary All'!$U$2),('Data From Arbor'!$S$2:$S$5000='Summary All'!$E$2)+('Data From Arbor'!$S$2:$S$5000='Summary All'!$U$2),('Data From Arbor'!$Q$2:$Q$5000='Summary All'!$E$2)+('Data From Arbor'!$Q$2:$Q$5000='Summary All'!$U$2))
V6,X6	V6	=+U6/$T$6
W6	W6	=SUMPRODUCT(('Data From Arbor'!$AS$2:$AS$5000='Summary All'!H2)+('Data From Arbor'!$AS$2:$AS$5000='Summary All'!W2),('Data From Arbor'!$BO$2:$BO$5000='Summary All'!H2)+('Data From Arbor'!$BO$2:$BO$5000='Summary All'!W2),('Data From Arbor'!$BU$2:$BU$5000='Summary All'!H2)+('Data From Arbor'!$BU$2:$BU$5000='Summary All'!W2),('Data From Arbor'!$BW$2:$BW$5000='Summary All'!H2)+('Data From Arbor'!$BW$2:$BW$5000='Summary All'!W2),('Data From Arbor'!$AU$2:$AU$5000='Summary All'!H2)+('Data From Arbor'!$AU$2:$AU$5000='Summary All'!W2),('Data From Arbor'!$BQ$2:$BQ$5000='Summary All'!H2)+('Data From Arbor'!$BQ$2:$BQ$5000='Summary All'!W2),('Data From Arbor'!$BI$2:$BI$5000='Summary All'!H2)+('Data From Arbor'!$BI$2:$BI$5000='Summary All'!W2),('Data From Arbor'!$BK$2:$BK$5000='Summary All'!H2)+('Data From Arbor'!$BK$2:$BK$5000='Summary All'!W2),('Data From Arbor'!$BS$2:$BS$5000='Summary All'!H2)+('Data From Arbor'!$BS$2:$BS$5000='Summary All'!W2),('Data From Arbor'!$AW$2:$AW$5000='Summary All'!H2)+('Data From Arbor'!$AW$2:$AW$5000='Summary All'!W2),('Data From Arbor'!$BA$2:$BA$5000='Summary All'!H2)+('Data From Arbor'!$BA$2:$BA$5000='Summary All'!W2),('Data From Arbor'!$AY$2:$AY$5000='Summary All'!H2)+('Data From Arbor'!$AY$2:$AY$5000='Summary All'!W2))
T6	T6	=COUNTIF('Data From Arbor'!$A$2:$A$1048576,"?*")
T7	T7	=COUNTIFS('Data From Arbor'!$C$2:$C$63,"Female")
T8	T8	=COUNTIFS('Data From Arbor'!$C$2:$C$63,"Male")
T9	T9	=COUNTIF('Data From Arbor'!$F$2:$F$63, "✓")
T10	T10	=COUNTIF('Data From Arbor'!$F$2:$F$63, "✕")
T11	T11	=COUNTIF('Data From Arbor'!$D$2:$D$63, "✓")
T12	T12	=COUNTIF('Data From Arbor'!$D$2:$D$63, "✕")
T13	T13	=COUNTIF('Data From Arbor'!$G$2:$G$63, "✓")
T14	T14	=COUNTIF('Data From Arbor'!$H$2:$H$63, "✓")
T15	T15	=COUNTIF('Data From Arbor'!$DG$2:$DG$63, "✓")