SOLVED

# Help with Complex Formula

Copper 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á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

# Re: Help with Complex Formula

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?

# Re: Help with Complex Formula

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

# Re: Help with Complex Formula

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

# Re: Help with Complex Formula

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

# Re: Help with Complex Formula

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

# Re: 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))``

# Re: Help with Complex Formula

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.

# Re: Help with Complex Formula

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.

# Re: Help with Complex Formula

or better yet define the data as a table and use table references

# Re: Help with Complex Formula

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

# Re: Help with Complex Formula

Thank you so much. It worked like a charm!

# Re: Help with Complex Formula

Thank you so much for another alternative using SUMPRODUCT!

# Re: Help with Complex Formula

Glad it worked. You can't go wrong with either of the solutions offered.
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

# Re: 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))``