Sumif with dynamic column less than or equal to (YTD)

[LisaMK]

Good afternoon, this is in regards to summarizing different general ledger accounts (rows) into categories to get a YTD amount for each category by adding up columns that are less than or equal to the current month. I used to have a workbook where I wrote this formula at my old job, and now I am at a new one trying to write it again and I can't seem to get it to work - if I recall it used index and match, I'm just having trouble factoring in the less than or equal bit. A visual representation is below, assume August is period 4 (I know it's the 2nd month but I use the '4' for another formula so I'm calling it period 4). Basically I want to make a formula for cell I4 where I can change the category in cell I2 and the period number in cell I3 and get a result in I4 that sums up all amounts in that category for year to date (includes the current period + prior periods). Can anyone help?

 

[Alex Blakenburg]

You probably had a pre-365 solution before and there are probably better ways of doing it but this might get you started and XL2BB may help get others to buy in.
Note: you will need to increase that the range of the columns to cover the max no of expected column.
In the filter it is anchored to the top left Amount column and then the index is trying to find the bottom right amount value to include in the range.

20210920 Variable Sum Range filtered.xlsx
	A	B	C	D	E	F	G	H	I	J
1				3	4
2				Jul-21	Aug-21			Category	Gross Media
3	Category	Account #	Account Name	Amount	Amount			Period	4
4	Gross Media	1100	Name 1100	100	100			YTD Amount	270
5	Other	1130	Name 1130
6	Affiliate	1135	Name 1135	75	80
7	Affiliate	1136	Name 1136	50	75
8	Programmatic & Ad Tech	1140	Name 1140	25	60
9	Gross Media	1005	Name 1005	60	10
10				310	325
11
Sheet1

Cell Formulas
Range	Formula
I4	I4	=SUM(FILTER($D$4:INDEX($D$9:$E$9,0,MATCH($I$3,$D$1:$E$1,0)),$A$4:$A$9=$I$2,0))
C4:C9	C4	="Name " & B4
D10:E10	D10	=SUM(D4:D9)

 

[LisaMK]

Thanks, Alex, I appreciate the repsonse.. Unfortunately I am getting a message in excel "That function isn't valid". I'm just wondering, what is the purpose of changing the values in column C to be "Name & B4"? Thanks.

 

[Alex Blakenburg]

Unfortunately I am getting a message in excel "That function isn't valid"

Your profile says you are using 365, so it should have worked. If you type "= Filter" into an empty cell, does it recognise the filter function ?

I'm just wondering, what is the purpose of changing the values in column C to be "Name & B4"?

None ! When we are sent a picture, instead of an XL2BB or a spreadsheet or even just a straight copy paste, step 1 is to laboriously manually recreate the data.
In your case, the data in that column was irrelevant to the formula, so rather than leave it blank altogether I semi-auto populated it.

 

[LisaMK]

Thanks, Alex.

We are on Office 365 here at my work but I tried to use "=Filter" and the only formula option I have is "=filterxml". I just checked and my version of Excel is Microsoft Office Professional Plus 2016 - not sure if that makes a difference?

And that makes sense about the account names, thanks.

 

[Alex Blakenburg]

I just checked and my version of Excel is Microsoft Office Professional Plus 2016 - not sure if that makes a difference?

Sadly, yes it does make a difference. 365 supports dynamic arrays and whole host of new function mostly revolving around that.

Try the below. I am just not sure whether it is working for me because I have 365 or not.

=SUM(IF($D$1:$E$1<=$I$3,IF($A$4:$A$10=$I$2,$D$4:$E$10)))

This should work too:

=SUMPRODUCT(($D$4:$E$10)*($D$1:$E$1<=$I$3)*($A$4:$A$10=$I$2))

 

[LisaMK]

The Sumproduct formula works, thank you Alex!

 

[Alex Blakenburg]

Glad I could help. Thanks for the feedback and for being specific in what worked for you.

 

[Fluff]

I just checked and my version of Excel is Microsoft Office Professional Plus 2016

In that case I would suggest you change your profile to show 2016, rather than 365. It saves members offering a solution that won't work & saves you the time testing that solution.