COUNTIF and VLOOKUP with multiple tables

[Chameleon64]

I have a table of children, which school they attend and which year group they belong to (Sheet1). I have a second table (Sheet2) with the list of schools and in which geographic area they're located.

I would like a summary table (Sheet3) which summarises the numbers of children by year group and area. I was thinking a combination of COUNTIF and VLOOKUP but examples I've found don't seem relevant to this particular problem.

For operational reasons too boring to go into, I don't want a column on Sheet1 which VLOOKUPs the area for each school. Can anyone come up with a formula for Sheet3? Many thanks in advance!

Sheet1	A	B	C
1	ChildName	SchoolName	YearGroup
2	Child_A	School_A	1
3	Child_B	School_B	1
4	Child_C	School_A	1
5	Child_D	School_A	2
6	Child_E	School_B	2
7	Child_F	School_A	2

<tbody>
</tbody>

Sheet2	A	B
1	SchoolName	Area
2	School_A	Area_1
3	School_B	Area_2

<tbody>
</tbody>

Sheet3	A	B	C
1	YearGroup	Area_1	Area_2
2	1	?	?
3	2	?	?

<tbody>
</tbody>

 

[Marcelo Branco]

Try

Array formula in Sheet3 B2 copied across and down
=SUM(IF(Sheet1!$C$2:$C$100=$A2,IF(ISNUMBER(MATCH(Sheet1!$B$2:$B$100,IF(Sheet2!$B$2:$B$100=B$1,Sheet2!$A$2:$A$100),0)),1)))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Chameleon64]

Marcelo, you are a genius, it works perfectly. Thank you so much!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.