Help with index match sumif and more.

[MrKris]

Hi there,
I’m a teacher who is looking to prepare better for the academic year. I teach a range of courses which require different amounts of time to setup and I’m keen to get a better understanding of the set up times for each course I teach.

I have attempted todo this is a matrix format, but I’m struggling to get the formula correct for my summary table.
Please can someone help me make this summary table work, it’s my first time on this forum so be gentle. ihave tried to explain everything on the spreadsheet in text boxes as best as I can.

I believe the solution is a mix of SUMIF, MATCH and INDEX, but the whole thing a bit confusing.

Matrix Example

Thanks,

 

[sandy666]

is that what you want?

 

[Snakehips]

Try like....

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	MATRIX									OVERALL SUMMARY
2	Modules	Duration	Plan Time	Cat A	Cat B	Cat C	Cat D			Duration	Plan Time
3	A1	30	10	x	x	x	x		Cat A	155	80
4	A2	35	30	x	x	x			Cat B	435	190
5	A3	90	40	x	x	x			Cat C	465	200
6	B1	70	30		x	x	x		Cat D	130	50
7	B2	100	20		x	x
8	B3	110	60		x	x
9	C1	30	10			x	x
Sheet1

Cell Formulas
Range	Formula
J3:K8	J3	=IFERROR(SUMIF(INDEX($D$3:$G$20,,ROWS(J$3:J3)),"x",B$3:B$20),"")

Hope that helps.

 

[Peter_SSs]

Welcome to the MrExcel board!

.. or consider transposing the summary table then the formulas in J3 & J4 copied across?

MrKris Course Matrix Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	MATRIX
2	Modules	Duration	Plan Time	Cat A	Cat B	Cat C	Cat D			Cat A	Cat B	Cat C	Cat D
3	A1	30	10	x	x	x	x		Duration	155	435	465	130
4	A2	35	30	x	x	x			Plan Time	80	190	200	50
5	A3	90	40	x	x	x
6	B1	70	30		x	x	x
7	B2	100	20		x	x
8	B3	110	60		x	x
9	C1	30	10			x	x
Sheet1

Cell Formulas
Range	Formula
J3:M3	J3	=SUMIF(D3:D9,"x",$B3:$B9)
J4:M4	J4	=SUMIF(D3:D9,"x",$C3:$C9)

 

[MrKris]

is that what you want?
View attachment 14143

Yes exactly that!

 

[sandy666]

Yes exactly that!

so use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Modules", "Duration", "Plan Time"}, "Category", "Value"),
    Group = Table.Group(Unpivot, {"Category"}, {{"Duration", each List.Sum([Duration]), type number}, {"Time", each List.Sum([Plan Time]), type number}}),
    SortAsc = Table.Sort(Group,{{"Category", Order.Ascending}})
in
    SortAsc

you can add more rows (Modules) and/or columns (Categories), you are not limited with rows/columns
after that just refresh green table

 

[MrKris]

Try like....

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	MATRIX									OVERALL SUMMARY
2	Modules	Duration	Plan Time	Cat A	Cat B	Cat C	Cat D			Duration	Plan Time
3	A1	30	10	x	x	x	x		Cat A	155	80
4	A2	35	30	x	x	x			Cat B	435	190
5	A3	90	40	x	x	x			Cat C	465	200
6	B1	70	30		x	x	x		Cat D	130	50
7	B2	100	20		x	x
8	B3	110	60		x	x
9	C1	30	10			x	x
Sheet1

Cell Formulas
Range	Formula
J3:K8	J3	=IFERROR(SUMIF(INDEX($D$3:$G$20,,ROWS(J$3:J3)),"x",B$3:B$20),"")

Hope that helps.

Absolutely perfect! Looks like I need to upskill in the index function.

Thank you so much!!!

 

[MrKris]

so use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Modules", "Duration", "Plan Time"}, "Category", "Value"),
    Group = Table.Group(Unpivot, {"Category"}, {{"Duration", each List.Sum([Duration]), type number}, {"Time", each List.Sum([Plan Time]), type number}}),
    SortAsc = Table.Sort(Group,{{"Category", Order.Ascending}})
in
    SortAsc

View attachment 14146
you can add more rows (Modules) and/or columns (Categories), you are not limited with rows/columns
after that just refresh green table

Ooh interesting,I’m going to look at this. I’ve already used one of the solutions above but as I upscale this to my full class list this may be a better solution.
thanks so much for your help.

 

[sandy666]

Ooh interesting,I’m going to look at this. I’ve already used one of the solutions above but as I upscale this to my full class list this may be a better solution.
thanks so much for your help.

You are welcome