Forum Discussion
Help with Complex Formula
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))
I think a formula can be drawn up for this situation. Why does the sum range for a given column vary? Is there something in the rows that might help us determine what to sum for a given column?
It's because column A:A has dates in ascending order and I need the sum starting on different dates forward. So ideally for 3 different start dates, I would have the 3 following formulas:
=SUMIF(1:1,"blábláblá*",377:843)
=SUMIF(1:1,"blábláblá*",452:843)
=SUMIF(1:1,"blábláblá*",637:843)
But these formulas are not working... But note that the range for each column in each of the three date intervals is the same...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))- 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.
