I’ve got a complicated formula that I don’t understand:
=map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,map(BZ1:CW1,lambda(Λ,let(x,index(sumifs('Ref4'!Q:Q,'Ref4'!G:G,Σ,--'Ref4'!P:P,">="&Λ,--'Ref4'!P:P,"<"&if(day(Λ)=1,offset(Λ,,1),eomonth(Λ,)))),if(x=0,,x)))))))
that is used to solve the problem described here: Arrayformula to SUM matching ID AND when date is between two dates
However, I would like to put this formula in a header because I’m constantly doing an advanced sort of the data (Data–>Sort Range—>Advanced Range Sorting Options) in the the sheet, which would move the location of this formula and therefore, mess up its calculations.
An example for an entirely different array formula I’ve used as a header in the past would look something like:
={"Total Harvest kg";
ARRAYFORMULA(SUMPRODUCT(('Ref4'!$G$2:$G = TEXT($A$2:$A, "0")) * 'Ref4'!$Q$2:$Q))}
When I tried:
={"Don't Delete";
map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,map(BZ1:CW1,lambda(Λ,let(x,index(sumifs('Ref4'!Q:Q,'Ref4'!G:G,Σ,--'Ref4'!P:P,">="&Λ,--'Ref4'!P:P,"<"&if(day(Λ)=1,offset(Λ,,1),eomonth(Λ,)))),if(x=0,,x)))))))
}
I get the error : #VALUE! In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
Ideally I could put this header/formula in BY1 or BZ1. If it is in BZ1 the header would still need to be the same date “01/01/24”
Here is the sheet: https://docs.google.com/spreadsheets/d/1k6nyu3xh0sLvbWjJ_vValAklzeTy72fvv3Cp0pEo–Y/edit?usp=sharing
You can’t solve this problem with the syntax you’re using because the arrays are mismatched in column sizes. Since the map runs from BZ to CW, there should be 24 columns in the corresponding header row.
@rockinfreakshow probably has an esoteric solution to this.
If it were me, I’d copy BZ1:CW1, paste it in a hidden tab, and use that sheet reference for the header row, placing the formula in BZ1. e.g.,
={dates!A1:X1;
map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,map(BZ1:CW1,lambda(Λ,let(x,index(sumifs('Ref4'!Q:Q,'Ref4'!G:G,Σ,--'Ref4'!P:P,">="&Λ,--'Ref4'!P:P,"<"&if(day(Λ)=1,offset(Λ,,1),eomonth(Λ,)))),if(x=0,,x)))))))
}