Forum Discussion
Solved
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...
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))
=SUMPRODUCT(($A$2:$A$18>=B$21)*ISNUMBER(SEARCH("blblbl*",$B$1:$F$1))*$B$2:$F$18)I've deleted the suggestion because it didn't take into account all conditions. An alternative for Patrick2788 solution could be SUMPRODUCT for those who don't have Office365 or Excel 2021. In the example a date can be entered in cells B21:D21 and the formula dynamically updates the results.
Thank you so much for another alternative using SUMPRODUCT!
