Counting Across Tables

[leefletcher]

Hello!

I am trying to count items but they are linked across tables.

Sheet 1 - Has the schools listed by city

Cities	Schools
Boston	Maple Elementary
Boston	Elm Middle School
Boston	Cedar Elementary
Los Angeles	Bamboo Elementary
New York	Pine High School
New York	Ficus High School

Sheet 2 - Shows which grade levels are in the school

Schools	Grades
Woodruff Elementary	1st-5th
Cedar Elementary	5th-6th
Elm Middle School	6th-8th
Ficus High School	9th-10th
Landrum High School	9th-12th
Northwest Elementary	k-5th

Sheet 3 - Tells me the grade level types that each grade level is assigned

Grades	Types
1st-5th	e
5th-6th	m
6th-8th	m
9th-10th	h
9th-12th	h
k-5th	e

How do I count the number of "types" in each town? Like this:

	e	m	h
Boston	1​	2​	0​
New York	0​	0​	2​
Los Angeles	1​	0​	0​

 

[leefletcher]

Since the schools have districts, I added a 3rd column and put the districts in there and tried to use this:
=SUM(IF($A$3:$A$8=$A30,IF(ISNUMBER(MATCH(($B$3:$B$8*C3:C8),IF(ISNUMBER(MATCH($B$12:$B$17,IF($B$21:$B$26=B$29,$A$21:$A$26),0)),($A$12:$A$17*C12:C17)),0)),1)))
Sadly, it didn't work.

Any thoughts on how to Match including the district since I have 2 schools in the state with the same name but are in different districts?

 

[leefletcher]

& not *!!

Thank you, so very much!!

 

[Domenic]

Can you post your revised tables, along with the expected results?

 

[leefletcher]

Domenic-

I got it to work with &, but here's what they look like:

Sheet 1 - Has the schools listed by city
Cities	Schools	District
Boston	Maple Elementary	Pear District 01
Boston	Elm Middle School	Pear District 01
Boston	Cedar Elementary	Pear District 01
Los Angeles	Bamboo Elementary	Apple District 01
New York	Pine High School	Orange District 01
New York	Ficus High School	Lime District 01
Sheet 2 - Shows which grade levels are in the school
Schools	Grades	District
Maple Elementary	1st-5th	Pear District 01
Cedar Elementary	5th-6th	Pear District 01
Elm Middle School	6th-8th	Pear District 01
Ficus High School	9th-10th	Lime District 01
Pine High School	9th-12th	Orange District 01
Bamboo Elementary	k-5th	Apple District 01
Sheet 3 - Tells me the grade level types that each grade level is assigned
Grades	Type
1st-5th	e
5th-6th	e
6th-8th	m
9th-10th	h
9th-12th	h
k-5th	e
How do I count the number of "types" in each town? Like this:
	e	m	h
Boston	2​	1​	0​
New York	0​	0​	2​
Los Angeles	1​	0​	0​

​

=SUM(IF($A$3:$A$8=$A30,IF(ISNUMBER(MATCH(($B$3:$B$8&$C$3:$C$8),IF(ISNUMBER(MATCH($B$12:$B$17,IF($B$21:$B$26=B$29,$A$21:$A$26),0)),($A$12:$A$17&$C$12:$C$17)),0)),1)))

I do have a follow-up question: Some schools won't have grades but they will have an "Other" tag (e.g., charter, alternative, technical). How do I first check to see if there is a "grade" and then use the "other" to apply the Type?

Sheet 1 - Has the schools listed by city
Cities	Schools	District
Boston	Maple Elementary	Pear District 01
Boston	Elm Middle School	Pear District 01
Boston	Cedar Elementary	Pear District 01
Los Angeles	Bamboo Elementary	Apple District 01
New York	Pine High School	Orange District 01
New York	Ficus High School	Lime District 01
Sheet 2 - Shows which grade levels are in the school
Schools	Grades	District	Other
Maple Elementary	1st-5th	Pear District 01
Cedar Elementary	5th-6th	Pear District 01
Elm Middle School	6th-8th	Pear District 01
Ficus High School	9th-10th	Lime District 01
Pine High School		Orange District 01	Charter
Bamboo Elementary	k-5th	Apple District 01
Sheet 3 - Tells me the grade level types that each grade level is assigned
Grades	Type1
1st-5th	e
5th-6th	e
6th-8th	m
9th-10th	h
9th-12th	h
k-5th	e
Charter	Other
How do I count the number of "types" in each town? Like this:
	e	m	h	Other
Boston	2​	1​	0​	0​
New York	0​	0​	2​	0​
Los Angeles	0​	0​	0​	1​

When I've come across this in other formulas, I've added an If statement at the beginning to see if the "Grades" field is full. If it is, then I use the formula to return the data from the "Grades" column; if it isn't, then I use the formula to return the data from the "Other" column.

 

[Domenic]

Try...

B31, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=SUM(IF($A$3:$A$8=$A31,IF(ISNUMBER(MATCH($B$3:$B$8&$C$3:$C$8,IF(ISNUMBER(MATCH($B$12:$B$17,IF($B$21:$B$27=B$30,$A$21:$A$27),0))+ISNUMBER(MATCH($D$12:$D$17,IF($B$21:$B$27=B$30,$A$21:$A$27),0)),$A$12:$A$17&$C$12:$C$17),0)),1)))

However, it probably would be a bit more efficient to split the solution into two formulas. The first one as per your amended formula...

B31, confirmed with CONTROL+SHIFT+ENTER, copied across to D31, and down:

=SUM(IF($A$3:$A$8=$A31,IF(ISNUMBER(MATCH($B$3:$B$8&$C$3:$C$8,IF(ISNUMBER(MATCH($B$12:$B$17,IF($B$21:$B$27=B$30,$A$21:$A$27),0)),$A$12:$A$17&$C$12:$C$17),0)),1)))

And then a second one for your Other column...

E31, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF($A$3:$A$8=$A31,IF(ISNUMBER(MATCH($B$3:$B$8&$C$3:$C$8,IF(ISNUMBER(MATCH($D$12:$D$17,IF($B$21:$B$27=E$30,$A$21:$A$27),0)),$A$12:$A$17&$C$12:$C$17),0)),1)))

Hope this helps!

 

[leefletcher]

Thanks, again. I was able to construct the 2 column solution from above, however, I found 2 schools that have a Grade and a Type. I need to figure out which one we want to count it as and then figure out how to eliminate the double counting.

 

[leefletcher]

One more twist: How do I add up the enrollment for each of the Levels?

Sheet 1 - Has the schools listed by city
Cities	Schools	District
Boston	Maple Elementary	Pear01
Boston	Elm Middle School	Pear01
Boston	Cedar Elementary	Pear01
Los Angeles	Bamboo Elementary	Pear01
New York	Pine High School	Pear01
New York	Ficus High School	Pear01
Sheet 2 - Shows which grade levels are in the school
Schools	Grades	District	Other	Enrollment
Maple Elementary	1st-5th	Pear01		400​
Cedar Elementary	5th-6th	Pear01		500​
Elm Middle School	6th-8th	Pear01		750​
Ficus High School	9th-10th	Pear01		1000​
Pine High School		Orange District 01	Charter	100​
Bamboo Elementary	k-5th	Pear01		250​
Sheet 3 - Tells me the grade level types that each grade level is assigned
Grades	Types
1st-5th	e
5th-6th	m
6th-8th	m
9th-10th	h
9th-12th	h
k-5th	e
How do I count the number of "types" in each town? Like this:
	e	m	h	Other
Boston	400​	1250​	0​	0​
New York	0​	0​	1000​	100​
Los Angeles	250​	0​	0​	0​