Forum Discussion
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áblá*". The sum needs to start in different lines. For example, for one case, I need the sum of amounts in rows 377 through 843 where the the header in row 1 has "blábláblá*". I tried:
=SUMIF(1:1,"blábláblá*",377:843)
but it is not working. I ended up with a workaround, which is to have a sum line 845 that sums the amounts of that column for rows 377 through 843, and then I use the formula:
=SUMIF(1:1,"blábláblá*",845:845)
But I would prefer not to have the workaround. Can anyone help?
Many thanks,
Alan
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))
- Try
=SUM(FILTER(377:843,1:1="blablabla",0))OliverScheurich did you delete your answer? I thought I saw it and then it was gone. If so you should have left it or repost it because that solution is needed if they do not have Excel 365.
=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.
- Didn't work
