maxif problem and filter problem

[Natit]

Hello,
In the attached, I try to get the max price (cell E2) and to filter (cell A7) according to the selection of group and sub group.
While with a specific subgroup it works fine, when I select the "all" option (in B2), it doesn't work.
Can someone help me to find what is not working right in my formulas?
Thx
Nati

excel_question.xlsx
	A	B	C	D	E	F
1	group to choose	subgroup to choose	total price	items	max price	average
2	group2	subgroup26	138,032.73	7	23,928.13	19,718.96
3
4
5
6	cat_num	data1	data2	code	price
7	2104	data49	data59	22800	17305.95
8	2711	data207	data217	22800	23928.13
9	2732	data217	data227	22800	23928.13
10	2753	data227	data237	22800	23928.13
11	2809	data245	data255	22800	19988.83
12	2858	data264	data274	22800	22367.6
13	8038	data676	data686	22800	6585.96
stats

Cell Formulas
Range	Formula
C2	C2	=LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,IFERROR(INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1),""), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$61,0)+16, Last_unit_row,SUMPRODUCT(MAX(ROW(parameters!$B:$B)*(parameters!$B:$B<>""))), Old_salary_col,Total_sheet!$E:$E, Saif_col,Total_sheet!$D:$D, All_units,IF(Main_next_unit_name="",SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Last_unit_row))),SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Main_next_unit_row-1)))), One_unit,SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Unit_row&":o"&Unit_row))), IF($B$2="all",All_units,One_unit))
D2	D2	=LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,IFERROR(INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1),""), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$61,0)+16, Last_unit_row,SUMPRODUCT(MAX(ROW(parameters!$B:$B)*(parameters!$B:$B<>""))), Old_salary_col,Total_sheet!$E:$E, Saif_col,Total_sheet!$D:$D, All_units,IF(Main_next_unit_name="",SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Last_unit_row))),SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Main_next_unit_row-1)))), One_unit,SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Unit_row&":o"&Unit_row))), IF($B$2="all",All_units,One_unit))
E2	E2	=LET( Start_row,16, find_row,MATCH($B$2,parameters!$B$17:$B$51,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
A7:E13	A7	=LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$52,0)+16, Saif_col,Total_sheet!$D:$D, Unit_seifim_all,INDIRECT("parameters!$d"&Main_unit_row_row&":$o"&Main_next_unit_row), Unit_seifim1,INDIRECT("parameters!$d"&Unit_row&":$o"&Unit_row), All_unit,FILTER(FILTER(Total_sheet!$A:$E,ISNUMBER(MATCH(Saif_col,Unit_seifim1,0))),{1,1,1,1,1}), One_unit,FILTER(FILTER(Total_sheet!$A:$E,ISNUMBER(MATCH(Saif_col,Unit_seifim1,0))),{1,1,1,1,1}), IF($B$2="all",All_unit,One_unit))
Dynamic array formulas.

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)

 

[StephenCrump]

It's impossible to say without seeing the other worksheets referenced in the formulae. And even then, I'm not sure I have the stamina to wade through these formulae ...

But I do note:

A7: =IF($B$2="all",All_unit,One_unit) where your LET() defines All_unit and One_unit identically! Presumably All_unit should refer to Unit_seifim_all rather than Unit_seifim1?

And if A7# contains All/filtered results, couldn't you simply have:

C2: =SUM(INDEX(A7#,,5))
D2: =ROWS(A7#)
E2: =MAX(INDEX(A7#,,5))

 

[Natit]

Ooops, you are right, I left mistakes at the LET().
Basically the question sums to the problem that the FILTER funcion, doesn't give results when the range refers to more then one row (like here: INDIRECT("parameters!$d47:$o52"), while it gives the right results when there is only one row (like here: INDIRECT("parameters!$d47:$o47").
Does it make sense?

 

[StephenCrump]

I suspect your problem is with the MATCH.

If I'm following this correctly, your A7 formula for All_unit, when corrected, will contain:
MATCH(Saif_col,Unit_seifim_all,0)

which will look something like: MATCH(Total_sheet!$D:$D, parameters!$d47:$o52,0)

MATCH's 2nd argument lookup_array needs to be a single row or single column, so this formula will return a column of N/A.

 

[Natit]

Now I get it.
Can you suggest a formula that will solve my problem?
Many many THX

 

[StephenCrump]

Can you suggest a formula that will solve my problem?

I can try, but it's not clear what you're doing when we can't see your layout.

Can you post a simple example to show how you want your MATCH to work?

 

[Natit]

Here is the sheet with all the formulas (I succeeded to solve the problem with he XL2BB).
The problem I would like to solve is how to make the Filter function works when I select the ALL option in cell B2.
thx
Nati

excel_question.xlsx
	A	B	C	D	E
1	group to choose	subgroup to choose	total price	items	average
2	group2	subgroup27	51,254.12	3	17,084.71
3
4
5
6	cat_num	data1	data2	code	price
7	2547	data145	data155	22500	22367.6
8	2773	data234	data244	22500	16455.22
9	2904	data287	data297	22500	12431.3
10
stats

Cell Formulas
Range	Formula
C2	C2	=LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,IFERROR(INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1),""), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$61,0)+16, Last_unit_row,SUMPRODUCT(MAX(ROW(parameters!$B:$B)*(parameters!$B:$B<>""))), Old_salary_col,Total_sheet!$E:$E, Saif_col,Total_sheet!$D:$D, All_units,IF(Main_next_unit_name="",SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Last_unit_row))),SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Main_next_unit_row-1)))), One_unit,SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Unit_row&":o"&Unit_row))), IF($B$2="all",All_units,One_unit))
D2	D2	=LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,IFERROR(INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1),""), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$61,0)+16, Last_unit_row,SUMPRODUCT(MAX(ROW(parameters!$B:$B)*(parameters!$B:$B<>""))), Old_salary_col,Total_sheet!$E:$E, Saif_col,Total_sheet!$D:$D, All_units,IF(Main_next_unit_name="",SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Last_unit_row))),SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Main_next_unit_row-1)))), One_unit,SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Unit_row&":o"&Unit_row))), IF($B$2="all",All_units,One_unit))
E2	E2	=$C$2/$D$2
A7:E9	A7	=LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$52,0)+16, Saif_col,Total_sheet!$D:$D, Unit_seifim_all,INDIRECT("parameters!$d"&Main_unit_row&":$o"&Main_next_unit_row), Unit_seifim1,INDIRECT("parameters!$d"&Unit_row&":$o"&Unit_row), All_unit,FILTER(FILTER(Total_sheet!$A:$E,ISNUMBER(MATCH(Saif_col,Unit_seifim_all,0))),{1,1,1,1,1}), One_unit,FILTER(FILTER(Total_sheet!$A:$E,ISNUMBER(MATCH(Saif_col,Unit_seifim1,0))),{1,1,1,1,1}), IF($B$2="all",All_unit,One_unit))
Dynamic array formulas.

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)

 

[Natit]

I did it

My problem was with the function in A7 : All_unit,FILTER(FILTER(Total_sheet!$A:$E,ISNUMBER(MATCH(Saif_col,Unit_seifim_all,0))),{1,1,1,1,1}),
Where the Match cannot work with an array but only with a row (in my formula).
The solution I found is:
All_unit,FILTER(FILTER(Total_sheet!$A:$E,(COUNTIF(Unit_seifim_all,Saif_col)>0)),{1,1,1,1,1}),

Thx to all the help I got here

Nati

 

[StephenCrump]

Great, I'm glad you got it working. Thanks for posting back.