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

LisaMK

New Member
Joined
Sep 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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?

1632108276521.png
 

Attachments

  • 1632108018425.png
    1632108018425.png
    20.8 KB · Views: 7

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,169
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
134
2Jul-21Aug-21CategoryGross Media
3CategoryAccount #Account NameAmountAmountPeriod4
4Gross Media1100Name 1100100100YTD Amount270
5Other1130Name 1130
6Affiliate1135Name 11357580
7Affiliate1136Name 11365075
8Programmatic & Ad Tech1140Name 11402560
9Gross Media1005Name 10056010
10310325
11
Sheet1
Cell Formulas
RangeFormula
I4I4=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:C9C4="Name " & B4
D10:E10D10=SUM(D4:D9)
 

LisaMK

New Member
Joined
Sep 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,169
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,169
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=SUM(IF($D$1:$E$1<=$I$3,IF($A$4:$A$10=$I$2,$D$4:$E$10)))

This should work too:
Excel Formula:
=SUMPRODUCT(($D$4:$E$10)*($D$1:$E$1<=$I$3)*($A$4:$A$10=$I$2))
 

LisaMK

New Member
Joined
Sep 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
The Sumproduct formula works, thank you Alex!
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,169
Office Version
  1. 365
Platform
  1. Windows
Glad I could help. Thanks for the feedback and for being specific in what worked for you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,914
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,673
Messages
5,904,392
Members
435,089
Latest member
blackstapler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top