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]

I see. Thank you for clarifying. I think I've fixed it.

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
Maple Elementary	1st-5th
Cedar Elementary	5th-6th
Elm Middle School	6th-8th
Ficus High School	9th-10th
Pine 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	2	1	0
New York	0	0	2
Los Angeles	1	0	0

 

[sandy666]

Cities	Schools		Schools	Grades		Grades	Types		Cities	e	m	h
Boston	Maple Elementary		Maple Elementary	1st-5th		1st-5th	e		Boston	1	2
Boston	Elm Middle School		Cedar Elementary	5th-6th		5th-6th	m		New York			2
Boston	Cedar Elementary		Elm Middle School	6th-8th		6th-8th	m
Los Angeles	Bamboo Elementary		Ficus High School	9th-10th		9th-10th	h
New York	Pine High School		Pine High School	9th-12th		9th-12th	h
New York	Ficus High School		Northwest Elementary	k-5th		k-5th	e

all your "tables" should be as Excel Tables (Ctrl+T) then doesn't matter where they are
here is the M step by step

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in
    Source

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content]
in
    Source

// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content]
in
    Source

// Merge1
let
    Source = Table.NestedJoin(Table1,{"Schools"},Table2,{"Schools"},"Table2",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table2", {"Grades"}, {"Grades"})
in
    Expand

// Merge2
let
    Source = Table.NestedJoin(Merge1,{"Grades"},Table3,{"Grades"},"Table3",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table3", {"Types"}, {"Types"}),
    Group = Table.Group(Expand, {"Cities", "Types"}, {{"Count", each Table.RowCount(_), type number}}),
    Filter = Table.SelectRows(Group, each ([Types] <> null)),
    Pivot = Table.Pivot(Filter, List.Distinct(Filter[Types]), "Types", "Count", List.Sum)
in
    Pivot

s666-PQ-Counting Across Tables.xlsx

Microsoft Excel Workbook

1drv.ms

 

[leefletcher]

Thank you. I'm too much of a "baby user" to know how to do anything with Power Qwerys, but I will look into it. I thought it might be too complicated to do with formulas.

 

[sandy666]

It looks complicated but it isn't, just merge tables by suitable columns, that's all
I suggest to learn Power Query from here

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

almost everything is there

but if you want formula solution maybe someone else will do that for you

Have a nice day

 

[Domenic]

@sandy666 has given you a great solution. PowerQuery should be much more efficient than any formula solution. Nevertheless, here's a formula solution. For simplicity, though, I have assumed that all of the relevant tables are located on the same worksheet, starting at cell A1. You'll need to amend the references accordingly.

Enter the following formula in cell B30, confirm with CONTROL+SHIFT+ENTER, and copy across and down:

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

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
Maple Elementary	1st-5th
Cedar Elementary	5th-6th
Elm Middle School	6th-8th
Ficus High School	9th-10th
Pine 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	0	0	0

Hope this helps!

 

[leefletcher]

@sandy666 has given you a great solution. PowerQuery should be much more efficient than any formula solution. Nevertheless, here's a formula solution. For simplicity, though, I have assumed that all of the relevant tables are located on the same worksheet, starting at cell A1. You'll need to amend the references accordingly.

Enter the following formula in cell B30, confirm with CONTROL+SHIFT+ENTER, and copy across and down:

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

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
Maple Elementary	1st-5th
Cedar Elementary	5th-6th
Elm Middle School	6th-8th
Ficus High School	9th-10th
Pine 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	0	0	0

Hope this helps!

Thank you. Do you see any problems with this working since the tables are on different worksheets?

 

[Domenic]

No, should fine...

 

[Domenic]

Actually, since you have the latest version of Excel, you should have access to dynamic array formulas, which are usually more efficient than regular array formulas. Therefore, you could try the following formula instead...

B30, confirmed with just ENTER, and copy across and down:

=IFERROR(ROWS(FILTER($B$3:$B$8,($A$3:$A$8=$A30)*(ISNUMBER(MATCH($B$3:$B$8,FILTER($A$12:$A$17,ISNUMBER(MATCH($B$12:$B$17,FILTER($A$21:$A$26,($B$21:$B$26=B$29)),0))),0))))),0)

Hope this helps!

 

[leefletcher]

Actually, since you have the latest version of Excel, you should have access to dynamic array formulas, which are usually more efficient than regular array formulas. Therefore, you could try the following formula instead...

B30, confirmed with just ENTER, and copy across and down:

=IFERROR(ROWS(FILTER($B$3:$B$8,($A$3:$A$8=$A30)*(ISNUMBER(MATCH($B$3:$B$8,FILTER($A$12:$A$17,ISNUMBER(MATCH($B$12:$B$17,FILTER($A$21:$A$26,($B$21:$B$26=B$29)),0))),0))))),0)

Hope this helps!

Thank you. The other one worked, but when I try this I get the "That function isn't valid" error and it highlights "Filter". We are using Microsoft Office Professional Plus 2019 (as of Monday, lol).

There are duplicate school names in my much larger data sheet (B3:B8, A12:A17), but they are in different districts. If I add districts into a C column, could I use ((B3:B8)*(C3:C8)) and ((A12:A17)*(C12:C17))?

 

[Domenic]

Just checked, and apparently the Filter function is only available in Office 365. So stick with the first one.