Apostrophes problem

[Natit]

This formula works fine:
=SUMPRODUCT(COUNTIF(Total_sheet!$D:$D,parameters!D35:F35)))

This formula doesn't work:
=LET(
Row_num,35,
SUMPRODUCT(COUNTIF(Total_sheet!$D:$D,"parameters!D"&Row_num&"arameters!f"&Row_num)))

What am I doing wrong?

THX
Nati

 

[RoryA]

You need INDIRECT if you're trying to use an address string like that:

=LET(
Row_num,35,
SUMPRODUCT(COUNTIF(Total_sheet!$D:$D,INDIRECT("parameters!D"&Row_num&":f"&Row_num))))

or you could use INDEX:

=LET(
Row_num,35,
SUMPRODUCT(COUNTIF(Total_sheet!$D:$D,INDEX(parameters!D:F,Row_num,0))))

 

[Natit]

The first one works perfect, and it is good for me.

May I challenge you with a more complex issue.
In the formula I add here, the One_unit works perfect, but the All_unit gives me the "calc" error.
Any suggested solution?

=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))

I know you prefer to see the file, but I have a problem to load it here, sorry.

and thx again,
Nati

 

[Fluff]

@Natit you already have a thread running for this new question, so you need to continue in that thread. Please do not duplicate you questions. Thanks
Thread closed.