# COUNTIF and VLOOKUP with multiple tables

#### Chameleon64

##### New Member
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>

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Marcelo Branco

##### MrExcel MVP
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

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

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Glad to help.

M.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,063
Messages
5,851,869
Members
431,470
Latest member
mikaelaleksandrowicz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back