Sum by group of cells

[Natit]

Hi,
In the file I attached, when I select a group and a specific subgroup, it works fine (total price, items, max price, average)
I'm looking for the formula that will do the same, when I select "all" in the "subgroup to choose" (in cell b2 in "stats" sheet).

Any help would be appreciated

Nait

mrexcel_question.xlsm
	A	B	C	D	E	F
1	group to choose	subgroup to choose	total price	items	max price	average
2	group1	subgroup17	52,237.38	4	19,423.83	13,059.35
stats

Cell Formulas
Range	Formula
C2	C2	=LET( find_row,MATCH($B$2,parameters!$B$17:$B$62,0)+16, subg1,INDEX(parameters!$D:$O,find_row,1), subg2,INDEX(parameters!$D:$O,find_row,2), subg3,INDEX(parameters!$D:$O,find_row,3), subg4,INDEX(parameters!$D:$O,find_row,4), subg5,INDEX(parameters!$D:$O,find_row,5), subg6,INDEX(parameters!$D:$O,find_row,6), subg7,INDEX(parameters!$D:$O,find_row,7), subg8,INDEX(parameters!$D:$O,find_row,8), subg9,INDEX(parameters!$D:$O,find_row,9), subg10,INDEX(parameters!$D:$O,find_row,10), subg11,INDEX(parameters!$D:$O,find_row,11), subg12,INDEX(parameters!$D:$O,find_row,12), price_col,Total_sheet!$E:$E, subg_col,Total_sheet!$D:$D, SUMIFS(price_col,subg_col,subg1)+SUMIFS(price_col,subg_col,subg2)+SUMIFS(price_col,subg_col,subg3)+SUMIFS(price_col,subg_col,subg4)+SUMIFS(price_col,subg_col,subg5)+SUMIFS(price_col,subg_col,subg6)+SUMIFS(price_col,subg_col,subg7)+SUMIFS(price_col,subg_col,subg8)+SUMIFS(price_col,subg_col,subg9)+SUMIFS(price_col,subg_col,subg10)+SUMIFS(price_col,subg_col,subg11)+SUMIFS(price_col,subg_col,subg12))
D2	D2	=LET( find_row,MATCH($B$2,parameters!$B$17:$B$53,0)+16, subg1,INDEX(parameters!$D:$O,find_row,1), subg2,INDEX(parameters!$D:$O,find_row,2), subg3,INDEX(parameters!$D:$O,find_row,3), subg4,INDEX(parameters!$D:$O,find_row,4), subg5,INDEX(parameters!$D:$O,find_row,5), subg6,INDEX(parameters!$D:$O,find_row,6), subg7,INDEX(parameters!$D:$O,find_row,7), subg8,INDEX(parameters!$D:$O,find_row,8), subg9,INDEX(parameters!$D:$O,find_row,9), subg10,INDEX(parameters!$D:$O,find_row,10), subg11,INDEX(parameters!$D:$O,find_row,11), subg12,INDEX(parameters!$D:$O,find_row,12), COUNTIFS(Total_sheet!$D:$D,subg1)+COUNTIFS(Total_sheet!$D:$D,subg2)+COUNTIFS(Total_sheet!$D:$D,subg3)+COUNTIFS(Total_sheet!$D:$D,subg4)+COUNTIFS(Total_sheet!$D:$D,subg5)+COUNTIFS(Total_sheet!$D:$D,subg6)+COUNTIFS(Total_sheet!$D:$D,subg7)+COUNTIFS(Total_sheet!$D:$D,subg8)+COUNTIFS(Total_sheet!$D:$D,subg9)+COUNTIFS(Total_sheet!$D:$D,subg10)+COUNTIFS(Total_sheet!$D:$D,subg11)+COUNTIFS(Total_sheet!$D:$D,subg12))
E2	E2	=LET( Start_row,16, find_row,MATCH($B$2,parameters!$B$17:$B$53,0)+Start_row, MAX(IF(Total_sheet!$D:$D=INDIRECT("parameters!$D"&find_row&":$O"&find_row),Total_sheet!E:E)))
F2	F2	=$C$2/$D$2

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=parameters!$A$3:$F$3
B2	List	=OFFSET(parameters!$A$3,1,MATCH($A2,parameters!$A$3:$F$3,0)-1,COUNTA(OFFSET(parameters!$A$3,1,MATCH($A2,parameters!$A$3:$F$3,0)-1,15)),1)

 

[earthworm]

Use subtotal option in excel