Forum Discussion
alanbr
Nov 02, 2022Copper Contributor
Help with Complex Formula
I need help with the following situation: I have row 1 with titles and all others with amounts, in many columns. I am trying to sum the amounts for every column where the header in row 1 has "blábláb...
- Nov 02, 2022
Here's my formula. I've attached a sample workbook. The term I'm looking for in the header is "Blue".
=LET(data,$E$2:$I$12,dates,$D$2:$D$12,header,$E$1:$I$1,period,FILTER(data,(dates>=L6)*(dates<L7)),filtered,FILTER(period,ISNUMBER(SEARCH("Blue",header))),SUM(filtered))
alanbr
Copper Contributor
Thank you so much! This is perfect in the sense that it is what I need. The only problem is that you have specified the ranges and I would like to keep only columns and rows specified for data, dates and header. Can you help me with this last step? For instance, dates would be A:A, header would be 1:1 and data would be something like 1:2048576 or A:ZZZ.
Patrick2788
Nov 02, 2022Silver Contributor
I can't recommend using entire column or row references with dynamic arrays because it's going to slow calculations considerably. You can use larger ranges like A1:Z10000, for example. I just wouldn't refer to entire columns/rows.
- mtarlerNov 02, 2022Silver Contributoror better yet define the data as a table and use table references
- alanbrNov 02, 2022Copper ContributorThank you so much. It worked like a charm!
- Patrick2788Nov 03, 2022Silver ContributorGlad it worked. You can't go wrong with either of the solutions offered.