Table summary formula

[Tigerexcel]

Hi all,

I have a number of discrete tables which need summarising. they are in the format shown below, there are no formulae in them.
I need a formula that will pick up the diploma name and return some of the totals in a compact list, something like:
Diploma Pass Rate Transition
Art 91% 261
Maths 91% 261
etc.

	Diploma of Art
Diploma	Start Trimester	Commence	Enrolled	Start / Compl Rate	Pass Rate	Avg Mark	Transition
Art	2018-2	29	4	69%	95%	65.0	19
Art	2018-1	76	5	74%	92%	62.9	55
Art	2017-3	10	1	60%	92%	63.8	6
Art	2017-2	36	2	64%	91%	61.4	22
Art	2017-1	111		83%	93%	63.6	90
	TOTAL	495	120	55%	91%	62.5	261
	Diploma of Maths
Diploma	Start Trimester	Commence	Enrolled	Start / Compl Rate	Pass Rate	Avg Mark	Transition
Maths	2018-2	29	4	69%	95%	65.0	19
Maths	2018-1	76	5	74%	92%	62.9	55
Maths	2017-3	10	1	60%	92%	63.8	6
Maths	2017-2	36	2	64%	91%	61.4	22
Maths	2017-1	111		83%	93%	63.6	90
	TOTAL	495	120	55%	91%	62.5	261

 

[GraH]

Hi, if the data is sitting in tables (and not ranges) then you can point to the total rows and tweak the formula.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Diploma	Pass Rate	Transition				Diploma of Art
2	Art	91%	192			Diploma	Start Trimester	Commence	Enrolled	Start / Compl Rate	Pass Rate	Avg Mark	Transition
3	Maths	85%	192			Art	2018-2	29	4	69%	95%	65.0	19
4						Art	2018-1	76	5	74%	92%	62.9	55
5						Art	2017-3	10	1	60%	92%	63.8	6
6						Art	2017-2	36	2	64%	91%	61.4	22
7						Art	2017-1	111		83%	93%	63.6	90
8						Total		495	120	55%	91%	62,5	192
9							Diploma of Maths
10						Diploma	Start Trimester	Commence	Enrolled	Start / Compl Rate	Pass Rate	Avg Mark	Transition
11						Maths	2018-2	29	4	69%	95%	65.0	19
12						Maths	2018-1	76	5	74%	92%	62.9	55
13						Maths	2017-3	10	1	60%	92%	63.8	6
14						Maths	2017-2	36	2	64%	91%	61.4	22
15						Maths	2017-1	111		83%	93%	63.6	90
16						Total		495	120	55%	85%	62,5	192
Sheet1

Cell Formulas
Range	Formula
B2:C3	B2	=INDIRECT($A2&"[[#Totals];["&B$1&"]]")
M8,M16	M8	=SUBTOTAL(109,[Transition])

 

[GraH]

Alternative is using INDEX in the reference form. This works if your tables always have the same number of lines?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Diploma	Pass Rate	Transition				Diploma of Art
2	Art	91%	261			Diploma	Start Trimester	Commence	Enrolled	Start / Compl Rate	Pass Rate	Avg Mark	Transition
3	Maths	91%	261			Art	2018-2	29	4	69%	95%	65.0	19
4						Art	2018-1	76	5	74%	92%	62.9	55
5						Art	2017-3	10	1	60%	92%	63.8	6
6						Art	2017-2	36	2	64%	91%	61.4	22
7						Art	2017-1	111		83%	93%	63.6	90
8							TOTAL	495	120	55%	91%	62.5	261
9
10							Diploma of Maths
11						Diploma	Start Trimester	Commence	Enrolled	Start / Compl Rate	Pass Rate	Avg Mark	Transition
12						Maths	2018-2	29	4	69%	95%	65.0	19
13						Maths	2018-1	76	5	74%	92%	62.9	55
14						Maths	2017-3	10	1	60%	92%	63.8	6
15						Maths	2017-2	36	2	64%	91%	61.4	22
16						Maths	2017-1	111		83%	93%	63.6	90
17							TOTAL	495	120	55%	91%	62.5	261
Sheet1

Cell Formulas
Range	Formula
B2:C3	B2	=INDEX(($G$3:$M$8,$G$12:$M$17),MATCH("Total",$G$3:$G$8,0),MATCH(B$1,$G$2:$M$2,0),ROW()-1)

 

[Tigerexcel]

Thanks Gra, appreciate that you've given me two ways to work this out. I've copied both straight onto Excel worksheets but I'm getting errors in the formulae, I'll work through this though. I did think about Index Match originally but couldn't get it to work.

 

[GraH]

With pleasure. Perhaps this helps you even further then.
=INDEX(($G$3:$M$8,$G$12:$M$17),MATCH("Total",$G$3:$G$8,0),MATCH(B$1,$G$2:$M$2,0),ROW()-1)
This part: between parenthesis, separated by "," is the range from each table you'd like to fetch data from
This should be rather straightforward: from any table is looks for the row where you have "total" in the related column
The third argument finds the correct column position, again from any table
ROW()-1 is the table/range position of the first argument. So if you are starting your formulas in let's say row 25, you need to adjust this. Perhaps try ROW(A1).

 

[Tigerexcel]

With pleasure. Perhaps this helps you even further then.
=INDEX(($G$3:$M$8,$G$12:$M$17),MATCH("Total",$G$3:$G$8,0),MATCH(B$1,$G$2:$M$2,0),ROW()-1)
This part: between parenthesis, separated by "," is the range from each table you'd like to fetch data from
This should be rather straightforward: from any table is looks for the row where you have "total" in the related column
The third argument finds the correct column position, again from any table
ROW()-1 is the table/range position of the first argument. So if you are starting your formulas in let's say row 25, you need to adjust this. Perhaps try ROW(A1).

Thanks for the explanation. I like your INDIRECT function, do you need to use subtotal as per your example?

 

[GraH]

You need to use a Total row, so the syntax I proposed worked. How you fill out that Total row does not matter. I went with the auto sum feature of tables, which uses subtotals. You can even hardcode values as well, though they are considered as text values then.
Bare in mind, indirect is a volatile function. And at each update, filtering, .... the calculation engine kicks in to recalculate the whole workbook. Like I said, you can also point to the correct total cell. If you have named your tables, which I did and I forgot to mention that in my first reply, you can replace that part of the formula. Try typing = TableName, and see what happens...

 

[Tigerexcel]

Thanks again Gra.
On a similar theme among the tables I need to consolidate and analyse are tables in the format below which I've simplified for the sake of the example. There are as many as 30 to 40 tables at a time to process. I need the formulae to return, it needs to be able to collect the 2019 - 3 results and pick up the diploma name from the table name (which is the part that I am having trouble with).

Diploma 2019-3 Pass rate Avg marks
Art 88 59
Maths 79 56

The data is in this format:

Table 3.2 - Diploma of Art
Commence Trimester	Pass Rate	A	B	Avg Mark
2019-3	88%​	X	X	58.6​
2019-2	91%​	X	X	62.7​
2019-1	87%​	X	X	60.4​
2018-3	90%​	X	X	63.0​
Total	91%​	X	X	62.5​
Table 3.3 - Diploma of Maths
Commence Trimester	Pass Rate	A	B	Avg Mark
2019-3	79%​	X	X	58.5​
2019-2	85%​	X	X	62.7​
2019-1	85%​	X	X	61.8​
2018-3	85%​	X	X	64.1​
Total	85%​	X	X	62.3​

 

[GraH]

Hi,
In this case I would use a Power Query solution. Assuming all those tables are on the same sheet called "Diplomas".

1. get data from file -> from workbook
2. navigate to the workbook (even if it is the one that's open )
3. in the navigator select the sheet "Diplomas" and hit Transform Data button
4. You may need to demote headers when the first table starts in row 2, PQ may pick this up as a column header.
5. Add a custom column, like in example below picture 1
6. With this new column highlighted, on the transform step select Fill and fill down
7. Like in Excel filter the table on Column 2 and Remove "null" from the selection
8. Promote headers (use first row as headers is a button on the Transform tab)
9. Filter out again, on any of the columns the repeated headers
10. Finally select the rows containing 2019 (which we can make a dynamic selection if required)
11. Move last column to first position (select header, and mouse right click, look for "Move" in the context menu and move to beginning)
12. Save and load to Excel as table or as pivot table.

Result looks like this

 

[Tigerexcel]

Thanks again again Gra. The Power Query option looks like it will do the trick. On my current computer the Power Query option doesn't come up but I will explore it further in the near future.
I was thinking if I cleaned up the titles on the tables such that all that was left was the actual Diploma name eg Maths, could we use an Index Match Match to return a pass rate for 2019 - 3 for example? In other words the Match parts of the formula would have to read the same column twice which I haven't seen work before. The Index and Match Match work great in two way lookups when you reference the intersection of a row and column but I can't get it to work when it refers to the same column. Possibly there is another function that could be used in this case?