# Calculate the sum of a dynamic filtered list in each column

1. ## Calculate the sum of a dynamic filtered list in each column

I would like to utilize the dynamic array functionality and create a formula that calculates the sum based on two criteria.

The formula that I have in N3 = SUM(FILTER(FILTER(H3#,H2#=N2),(G3#=M3))). Is there a way to make this spill down and to the right?

I have Office 365, but do not have the LAMBDA function.

test_sheet.PNG

Thanks!  Register To Reply

2. ## Re: Calculate the sum of a dynamic filtered list in each column

Welcome to the forum. LAMBDA is available on the insider programme - join up and you'll get it now.

Is this just an exercise in using the new functions, or would an older approach do?  Register To Reply

3. ## Re: Calculate the sum of a dynamic filtered list in each column

I am on a corporate machine and I believe they have blocked access to the insider program and this will be a shared report amongst other users that also will not have the LAMBDA function.

I am trying to create a dynamic report, the number of rows and columns could vary greatly. So I am trying to avoid dragging the formula.

If there is an older approach that accomplishes this then yes that works for me!  Register To Reply

4. ## Re: Calculate the sum of a dynamic filtered list in each column

You can't spill this, but paste it into the top left cell:

=SUMPRODUCT((\$G\$3#=\$M3)*(\$H\$2#=N\$2),\$H\$3#)

Then select that cell - CTRL+C - select the entire range - right-click - paste special - formulae.

Or just drag it. Sorry!  Register To Reply

5. ## Re: Calculate the sum of a dynamic filtered list in each column

If I alter the formula in N3 to be =FILTER(FILTER(H3#,H2#=N2#),(G3#=M3)) it spills to the four columns. Instead of getting the data for each column, I would like for it to give me the sum of each column.

revised_formula.PNG

Are you saying that this is not possible?

Thanks!  Register To Reply

6. ## Re: Calculate the sum of a dynamic filtered list in each column

Thats not the formula I gave you, though, is it?  Register To Reply

7. ## Re: Calculate the sum of a dynamic filtered list in each column Originally Posted by AliGW Thats not the formula I gave you, though, is it?
The original post asked if there is a way to avoid dragging the formula.

In your post, you said your formula has to be dragged or copied.

I have provided some more detail to show what I am hoping to accomplish.

Are you thinking that it could be modified to accomplish what I want?  Register To Reply

8. ## Re: Calculate the sum of a dynamic filtered list in each column

No. I asked if an older approach would do and you said that it would.

I dont know enough yet about the new dynamic formulae to say that its not possible, but I have not been able to do it.

I have given a solution that requires copying the old way. Thats the best I have - sorry  Register To Reply

9. ## Re: Calculate the sum of a dynamic filtered list in each column

Thanks for the try!  Register To Reply

10. ## Re: Calculate the sum of a dynamic filtered list in each column

So some compromise will be needed if you want a spilled array for your sums; we can unpivot your Dataset and then use SUMIFS on the unpivoted data.
Screenshot 2021-04-30 091455.png
In your example spreadsheet you create spilled formulas that replicate the layout of your dataset; try replacing that with this formula in H2:
Formula:  `Please Login or Register  to view this content.`

Then you can just SUMIFs on the spilled unpivoted data in N3:

Formula:  `Please Login or Register  to view this content.`  Register To Reply