Sumif based on column headers then converted to a table

[ExcelAmateur2020]

Hope I can explain this properly

Tab 1 has headed columns 1 - 38 (any football fans may recognise this as the number of gameweeks in the premier league season!). Each row then contains a number (1 - 5) based on the difficulty of a team's fixture in that week.

I want tab 2 to allow the user to enter a "from" and "to" week to see the total difficulty in that range (ie from 5 - 10 would return the values from columns headed 5,6,7,8,9&10).

Any help appreciated

 

[RAJESH NATH]

Can you share your sheet, to help you better.

 

[ExcelAmateur2020]

Complete novice so this may not work and I can not work out how to share a sheet sorry but here we go. In image 2 I want to be able to update values in B1 and B2 to then sum the equivalent values. For example based on current parameters ARS should read 7 (1 + 1 +5) in image 2 where I currently have a sumifs formula that is not working!

 

[sandy666]

with Power Query aka Get&Transform

Labels	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18		From		Labels	Sum
ARS	1	1	5	4	5	4	5	1	1	3	4	3	3	2	4	2	1	3		1		ARS	7
AVL	10	4	1	5	4	1	3	5	2	1	1	3	3	1	3	4	5	4				AVL	15
BHA	4	1	5	2	3	1	4	3	1	5	3	4	1	4	1	5	3	5		To		BHA	10
BUR	10	4	3	1	1	4	4	2	3	5	2	5	1	3	1	4	1	5		3		BUR	17
CHE	2	5	1	3	3	5	3	4	1	4	1	2	3	1	5	1	5	4				CHE	8
CRY	3	5	2	4	2	1	3	1	3	1	1	4	1	5	1	4	4	5				CRY	10
EVE	4	1	3	2	5	3	1	5	1	1	3	4	4	5	4	5	1	3				EVE	8
FUL	5	1	1	3	4	3	1	1	2	4	5	5	2	1	3	4	3	5				FUL	7
LEE	5	1	4	5	3	1	4	3	5	2	4	1	1	5	3	1	4	3				LEE	10
LEI	1	3	5	1	1	5	1	3	5	1	4	2	2	4	5	3	1	4				LEI	9
LIV	1	4	5	1	2	4	1	5	4	2	3	1	4	3	1	1	3	3				LIV	10
MCI	10	3	4	1	5	1	4	5	4	3	1	5	1	3	1	2	4	2				MCI	17
MUN	10	3	2	4	1	4	5	2	1	3	1	5	4	1	4	3	1	1				MUN	15
NEW	1	2	4	3	5	3	2	3	4	3	1	1	1	1	5	5	4	4				NEW	7
SHU	3	1	1	5	1	5	5	4	1	1	4	3	5	2	2	3	3	1				SHU	5
SOU	3	4	3	1	4	2	1	1	3	5	2	4	5	5	1	1	5	1				SOU	10
TOT	2	3	1	5	1	3	2	1	5	4	5	3	5	4	3	1	1	1				TOT	6
WBA	4	2	4	3	3	2	1	4	5	4	3	1	5	1	5	1	5	1				WBA	10
WHU	1	5	3	4	4	5	5	1	4	1	5	1	3	4	2	3	2	1				WHU	9
WOL	4	5	1	1	1	1	3	4	3	5	5	1	4	3	4	5	2	2				WOL	10

let
    From = Excel.CurrentWorkbook(){[Name="From"]}[Content],
    To = Excel.CurrentWorkbook(){[Name="To"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Labels"}, "Attribute", "Value"),
    Type = Table.TransformColumnTypes(UOC,{{"Attribute", Int64.Type}}),
    Select = Table.AddColumn(Type, "Select", each List.Select({[Attribute]}, each (_ >= From[From]{0} and _ <= To[To]{0}))),
    Expand = Table.ExpandListColumn(Select, "Select"),
    FilterNull = Table.SelectRows(Expand, each ([Select] <> null)),
    Group = Table.Group(FilterNull, {"Labels"}, {{"Sum", each List.Sum([Value]), type number}})
in
    Group

 

[ExcelAmateur2020]

with Power Query aka Get&Transform

Labels	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18		From		Labels	Sum
ARS	1	1	5	4	5	4	5	1	1	3	4	3	3	2	4	2	1	3		1		ARS	7
AVL	10	4	1	5	4	1	3	5	2	1	1	3	3	1	3	4	5	4				AVL	15
BHA	4	1	5	2	3	1	4	3	1	5	3	4	1	4	1	5	3	5		To		BHA	10
BUR	10	4	3	1	1	4	4	2	3	5	2	5	1	3	1	4	1	5		3		BUR	17
CHE	2	5	1	3	3	5	3	4	1	4	1	2	3	1	5	1	5	4				CHE	8
CRY	3	5	2	4	2	1	3	1	3	1	1	4	1	5	1	4	4	5				CRY	10
EVE	4	1	3	2	5	3	1	5	1	1	3	4	4	5	4	5	1	3				EVE	8
FUL	5	1	1	3	4	3	1	1	2	4	5	5	2	1	3	4	3	5				FUL	7
LEE	5	1	4	5	3	1	4	3	5	2	4	1	1	5	3	1	4	3				LEE	10
LEI	1	3	5	1	1	5	1	3	5	1	4	2	2	4	5	3	1	4				LEI	9
LIV	1	4	5	1	2	4	1	5	4	2	3	1	4	3	1	1	3	3				LIV	10
MCI	10	3	4	1	5	1	4	5	4	3	1	5	1	3	1	2	4	2				MCI	17
MUN	10	3	2	4	1	4	5	2	1	3	1	5	4	1	4	3	1	1				MUN	15
NEW	1	2	4	3	5	3	2	3	4	3	1	1	1	1	5	5	4	4				NEW	7
SHU	3	1	1	5	1	5	5	4	1	1	4	3	5	2	2	3	3	1				SHU	5
SOU	3	4	3	1	4	2	1	1	3	5	2	4	5	5	1	1	5	1				SOU	10
TOT	2	3	1	5	1	3	2	1	5	4	5	3	5	4	3	1	1	1				TOT	6
WBA	4	2	4	3	3	2	1	4	5	4	3	1	5	1	5	1	5	1				WBA	10
WHU	1	5	3	4	4	5	5	1	4	1	5	1	3	4	2	3	2	1				WHU	9
WOL	4	5	1	1	1	1	3	4	3	5	5	1	4	3	4	5	2	2				WOL	10

let
    From = Excel.CurrentWorkbook(){[Name="From"]}[Content],
    To = Excel.CurrentWorkbook(){[Name="To"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Labels"}, "Attribute", "Value"),
    Type = Table.TransformColumnTypes(UOC,{{"Attribute", Int64.Type}}),
    Select = Table.AddColumn(Type, "Select", each List.Select({[Attribute]}, each (_ >= From[From]{0} and _ <= To[To]{0}))),
    Expand = Table.ExpandListColumn(Select, "Select"),
    FilterNull = Table.SelectRows(Expand, each ([Select] <> null)),
    Group = Table.Group(FilterNull, {"Labels"}, {{"Sum", each List.Sum([Value]), type number}})
in
    Group

This is obviously absolutely amazing but I have literally no idea how to get that in to the sheet... I was really hoping to achieve it with a formula rather than macro / power query. Perhaps that is not possible. Seriously thanks so much though.

 

[sandy666]

This is obviously absolutely amazing but I have literally no idea how to get that in to the sheet... I was really hoping to achieve it with a formula rather than macro / power query. Perhaps that is not possible. Seriously thanks so much though.

sure, good luck with formula
anyway here is an example: s666-PQ-Sum selected rows.xlsx
after change From / To right click on green table and select Refresh

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18		From	1		Sum
2	ARS		1	1	5	4	5	4	5	1	1	3	4	3	3	2	4	2	1	3		To	3	ARS	7
3	AVL		10	4	1	5	4	1	3	5	2	1	1	3	3	1	3	4	5	4				AVL	15
4	BHA		4	1	5	2	3	1	4	3	1	5	3	4	1	4	1	5	3	5				BHA	10
5	BUR		10	4	3	1	1	4	4	2	3	5	2	5	1	3	1	4	1	5				BUR	17
6	CHE		2	5	1	3	3	5	3	4	1	4	1	2	3	1	5	1	5	4				CHE	8
7	CRY		3	5	2	4	2	1	3	1	3	1	1	4	1	5	1	4	4	5				CRY	10
8	EVE		4	1	3	2	5	3	1	5	1	1	3	4	4	5	4	5	1	3				EVE	8
9	FUL		5	1	1	3	4	3	1	1	2	4	5	5	2	1	3	4	3	5				FUL	7
10	LEE		5	1	4	5	3	1	4	3	5	2	4	1	1	5	3	1	4	3				LEE	10
11	LEI		1	3	5	1	1	5	1	3	5	1	4	2	2	4	5	3	1	4				LEI	9
12	LIV		1	4	5	1	2	4	1	5	4	2	3	1	4	3	1	1	3	3				LIV	10
13	MCI		10	3	4	1	5	1	4	5	4	3	1	5	1	3	1	2	4	2				MCI	17
14	MUN		10	3	2	4	1	4	5	2	1	3	1	5	4	1	4	3	1	1				MUN	15
15	NEW		1	2	4	3	5	3	2	3	4	3	1	1	1	1	5	5	4	4				NEW	7
16	SHU		3	1	1	5	1	5	5	4	1	1	4	3	5	2	2	3	3	1				SHU	5
17	SOU		3	4	3	1	4	2	1	1	3	5	2	4	5	5	1	1	5	1				SOU	10
18	TOT		2	3	1	5	1	3	2	1	5	4	5	3	5	4	3	1	1	1				TOT	6
19	WBA		4	2	4	3	3	2	1	4	5	4	3	1	5	1	5	1	5	1				WBA	10
20	WHU		1	5	3	4	4	5	5	1	4	1	5	1	3	4	2	3	2	1				WHU	9
21	WOL		4	5	1	1	1	1	3	4	3	5	5	1	4	3	4	5	2	2				WOL	10
Master

Cell Formulas
Range	Formula
X2:X21	X2	=UNIQUE(A2:A21)
Y2:Y21	Y2	=SUM(FILTER(FILTER($C$2:$T$21,$A$2:$A$21=$X2),($C$1:$T$1>=$W$1)*($C$1:$T$1<=$W$2)))
Dynamic array formulas.

 

[ExcelAmateur2020]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18		From	1		Sum
2	ARS		1	1	5	4	5	4	5	1	1	3	4	3	3	2	4	2	1	3		To	3	ARS	7
3	AVL		10	4	1	5	4	1	3	5	2	1	1	3	3	1	3	4	5	4				AVL	15
4	BHA		4	1	5	2	3	1	4	3	1	5	3	4	1	4	1	5	3	5				BHA	10
5	BUR		10	4	3	1	1	4	4	2	3	5	2	5	1	3	1	4	1	5				BUR	17
6	CHE		2	5	1	3	3	5	3	4	1	4	1	2	3	1	5	1	5	4				CHE	8
7	CRY		3	5	2	4	2	1	3	1	3	1	1	4	1	5	1	4	4	5				CRY	10
8	EVE		4	1	3	2	5	3	1	5	1	1	3	4	4	5	4	5	1	3				EVE	8
9	FUL		5	1	1	3	4	3	1	1	2	4	5	5	2	1	3	4	3	5				FUL	7
10	LEE		5	1	4	5	3	1	4	3	5	2	4	1	1	5	3	1	4	3				LEE	10
11	LEI		1	3	5	1	1	5	1	3	5	1	4	2	2	4	5	3	1	4				LEI	9
12	LIV		1	4	5	1	2	4	1	5	4	2	3	1	4	3	1	1	3	3				LIV	10
13	MCI		10	3	4	1	5	1	4	5	4	3	1	5	1	3	1	2	4	2				MCI	17
14	MUN		10	3	2	4	1	4	5	2	1	3	1	5	4	1	4	3	1	1				MUN	15
15	NEW		1	2	4	3	5	3	2	3	4	3	1	1	1	1	5	5	4	4				NEW	7
16	SHU		3	1	1	5	1	5	5	4	1	1	4	3	5	2	2	3	3	1				SHU	5
17	SOU		3	4	3	1	4	2	1	1	3	5	2	4	5	5	1	1	5	1				SOU	10
18	TOT		2	3	1	5	1	3	2	1	5	4	5	3	5	4	3	1	1	1				TOT	6
19	WBA		4	2	4	3	3	2	1	4	5	4	3	1	5	1	5	1	5	1				WBA	10
20	WHU		1	5	3	4	4	5	5	1	4	1	5	1	3	4	2	3	2	1				WHU	9
21	WOL		4	5	1	1	1	1	3	4	3	5	5	1	4	3	4	5	2	2				WOL	10
Master

Cell Formulas
Range	Formula
X2:X21	X2	=UNIQUE(A2:A21)
Y2:Y21	Y2	=SUM(FILTER(FILTER($C$2:$T$21,$A$2:$A$21=$X2),($C$1:$T$1>=$W$1)*($C$1:$T$1<=$W$2)))
Dynamic array formulas.

Smashed it. This works perfectly thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.