SOLVED

Help with Complex Formula

Occasional Contributor

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

13 Replies

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?

Try
=SUM(FILTER(377:843,1:1="blablabla",0))

@Quadruple_Pawn  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.

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...

Didn't work
best response confirmed by Grahmfs13 (Microsoft)
Solution

@alanbr 

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.
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.
or better yet define the data as a table and use table references

@mtarler 

=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.

sumproduct.JPG

 

Thank you so much. It worked like a charm!
Thank you so much for another alternative using SUMPRODUCT!
Glad it worked. You can't go wrong with either of the solutions offered.