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​

 

[Ezguy4u]

leefletcher Let's get the ball rolling. Now there are probably more solutions to this problem. This will at least get the discussion going. This might also generate more questions than answers. Usually some of the A Students will weigh in and come up with their solutions. Also note that Boston has 2 Elementary schools.​

Book1
	A	B	C	D
1		Elementary	Middle	High
2	Boston	2	1	0
3	New York	0	0	2
4	Los Angeles	1	0	0
Results

Cell Formulas
Range	Formula
B2:D4	B2	=COUNTIFS(Sheet1!$A$2:$A$7,$A2,Sheet1!$B$2:$B$7,"*"&B$1&"*")

 

[leefletcher]

leefletcher Let's get the ball rolling. Now there are probably more solutions to this problem. This will at least get the discussion going. This might also generate more questions than answers. Usually some of the A Students will weigh in and come up with their solutions. Also note that Boston has 2 Elementary schools.​

Book1
	A	B	C	D
1		Elementary	Middle	High
2	Boston	2	1	0
3	New York	0	0	2
4	Los Angeles	1	0	0
Results

Cell Formulas
Range	Formula
B2:D4	B2	=COUNTIFS(Sheet1!$A$2:$A$7,$A2,Sheet1!$B$2:$B$7,"*"&B$1&"*")

Thank you for replying and for catching my mistake on the solution table. My struggle is that the 3 tables with the data are all on different worksheets (provided and updated by others).

 

[sandy666]

so...
1. update your profile about Excel version and OS
2. post proper examples using XL2BB
3. one workbook, 3 different sheets, correct?

 

[leefletcher]

Thank you. I updated version and OS. Our Admins don't allow Add-ons, however.

Yes, the data is across 3 different sheets.

 

[sandy666]

how your examples look like really? (just post it again properly)

Also note that Boston has 2 Elementary schools.

 

[leefletcher]

Sheet 1 - Cities with schools

Sheet 2 - Schools with Grades

Sheet 3 - Grades with Grade Types

Need final sheet with Cities counting Grade Types

I've been able to accomplish this by adding columns to Sheets 1-3, but I don't own those sheets and any edits will be lost when new data is provided.

 

[sandy666]

Thanks for updating but I don't want to retype your data from the pictures, post examples as you did in the first post but with a proper data

 

[leefletcher]

Thanks for updating but I wont retype your data from the pictures, post examples as you did in the first post but with a proper data

The actual (proper?) data is private information so I can't copy and paste. The only change would be that the cities are mental health clinics. We have 500+ clinicians in 1300+ schools across the state. Those clinicians work out of clinics which reside in cities. The data I've provided is a basic version. Maybe I don't understand what you mean by "proper data".

 

[sandy666]

Also note that Boston has 2 Elementary schools.

Thank you for replying and for catching my mistake on the solution table

so your data in the first post are correct or not?
proper data means correct structure and type not your original data which can be classified. It can be generic data.

ok, got it. I'll try now