Excel Dynamic YTD Calculations: OFFSET, SUMPRODUCT & SUM

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover the power of Excel formulas to dynamically calculate year-to-date figures for volume and price. Ideal for analysts, accountants, and anyone seeking to enhance their Excel skills!
⬇️ Grab the workbook here: pages.xelplus.com/sumproduct-...
🔍 What's Inside:
▪️ Dynamic Formula for Year-to-Date Volume: Learn to use OFFSET combined with SUM functions to create a formula that dynamically adjusts as you drag it across months and years, avoiding manual adjustments.
▪️ Avoiding Helper Cells with SUMPRODUCT: Discover how to use SUMPRODUCT to eliminate the need for helper cells while calculating year-to-date figures for prices.
▪️ Handling Complex Data Sets: Understand how to efficiently manage complex data with overlapping years, ensuring your calculations are accurate and dynamic.
▪️ Deep Dive into OFFSET Function: Master the OFFSET function to create dynamic ranges that automatically adjust based on the month and year.
▪️ Real-World Application: Apply these techniques using real data, like monthly volumes and prices, for practical insights and analysis.
You'll learn how to use Excel formulas dynamically to analyze monthly data over several years.
Here's what you'll learn:
Combining Formulas for Dynamic Results: Discover how to use product and offset formulas together for more efficient data handling.
Year-to-Date Calculations: Learn to calculate year-to-date figures for both volume and price data, a key skill in data analysis.
Dealing with Complex Price Data: Understand the complexities of calculating average prices and how to tackle them effectively.
Avoiding Common Mistakes: Get insights into avoiding errors when dragging formulas across different months and years.
Dynamic Ranges with Offset Formula: Explore how the offset formula can dynamically adjust ranges, enhancing the flexibility of your Excel sheets.
Simplifying with SumProduct: See how the sum product formula can replace helper cells, making your data cleaner and more manageable.
Practical Examples and Demonstrations: The video provides clear, step-by-step examples to ensure you can apply these techniques to your own data.
I'll show you a way of writing YTD formulas (in this specific case for volume and price) when your data set goes over a few years. i.e. each month occurs more than once. If you were doing this manually, you'll need to revise your formula in the middle of your data set to make your range start from January of the next year. OFFSET allows you to create dynamic ranges - when you use this with the Month() and SUM() functions, you've created a smart formula that recognizes when the data goes over to the next year and revises your YTD calculation.
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/sumproduct-ytd-file

[@GosCee]

Leila, I love using Excel formulas and you're inspiring me with everyone of your videos. Thanks again for sharing.

[@marmat7072]

Awesome, I always avoid Offset, but used helper cells. This is so easy and simple the way you lay it out. Thanks again

[@raghuv7114]

No more words for you.my inspiration of learning level becoming infinite.

[@DrSougataBanerjee]

Quite an 'out-of-the-box' thinking - specially the part where you actually disguise (read, custom-format) a date to show as months. I personally think that was THE KEY to the whole solution. Kudos to your thought process!!

[@LeilaGharani]

Thank you for your feedback. Glad you like the tutorial.

[@zahirbabiker]

Thank you so much for your training series, I converted month name to number by using MONTH(DATEVALUE(Month_Name&" 1")))

[@xodrinker]

Extraordinary elegant Leila, Thanks!

[@jmasui]

Thank you - one of better method of calculating YTD coupled with easy to follow explanation.

[@bkkhatri2]

Hi Leila Thank you for your useful and powerful video. I learnt alot from them and still learning.

[@robsonnvula9285]

Great tutorial from Leila as always!! Looks so simple the way you explain it

[@LeilaGharani]

Thank you! 😊

[@maneprashant642]

Thanks Leila for the useful tools info.

[@lawrenceg7341]

Hello Leila, You are my go-to excel learning resource, it seems you have a magic in explaining the concept really really well. I know this is an old video but would you have the practice workbook available for this one?

[@Yuuuuuuuwei]

This is helpful and clear! and I really like the case you make. It makes a lot of sense.

[@LeilaGharani]

Glad you like it. Thank you for your comment.

[@Tattysnuc]

I came across a similar issue and this has given me some new ideas on how to solve the problem. Many thanks. Great to be learning once again about Excel :)

[@LeilaGharani]

Great to hear!

[@zakeermohamedkhan8750]

Leila, Slowly I am discovering the depth of intelligence in you. Amazing. Keep it up.

[@LeilaGharani]

I'm glad to hear that :)

[@louiselane806]

Hi Leila, you’re my go to when I need a formula, thank you for your great videos, people think I’m an excel wiz thanks to you 🙂. This one will help me with a ytd budget calculation, quick question can I use this with a vlookup, and how would that work, vlookup first?

[@marcbousquet9742]

Hello Leila, Nice videos and i have to tell you that, even I'm very "fluent" in excel, i discovered interesting things with your videos. I would like to share a quick enhancement of your "offset" formula. You wrote in B15 : =SUM(OFFSET(B7;0;0;1;-MONTH(B6)):B7) but the ":B7", within the SUM function, is useless as you are using the WIDTH property of the offset. you could have wrote =SUM(OFFSET(B7;0;0;1;-MONTH(B6))) it would ave worked the same. if you still want to reference the END of the range, which can be quite handy while reading at the formula, you could just offset the column part of the range without touching the WIDTH =SUM(OFFSET(B7;0;1-MONTH(B6)):B7) Note that there is a 1 before "-MONTH" because in January we do not want to slide left, in feb we want to slide just one column and so on. Thanks,

[@mrzorrombo]

Very helpful video! Pls share how to handle YTD if fiscal year starts from any other month

[@keishrich]

Thanks Leila! In my situation, I wanted to show YTD depreciation expense in one cell as the year progresses for a depreciation schedule with the monthly depreciation for all assets pre-calculated. Plus, my financial year is Feb - Jan. Your videos on the OFFSET function helped heaps, even though they didn't provide the exact solution I was looking for. My formula is: =SUM(OFFSET(AZ,0,1,1,IF(MONTH(D5)>=2,MONTH(D5)-1,MONTH(D5)+1))

[@LeilaGharani]

You're welcome Keisha! Great you adjusted it to fit your needs :)

[@FocusedSpender]

I'm going to use this formula at work tomorrow!

[@keishrich]

For the last argument in the IF function change it to 12, or when January comes, the the width for the OFFSET function will be 2 instead of 12. (Note: Reference D5 is for current reporting period in date format, for e.g. 6/30/2018). Revised formula: =SUM(OFFSET(Z10,0,1,1,IF(MONTH(D5)>=2,MONTH(D5)-1,12))) This formula will automatically sum year to date depreciation in (one cell) from Feb to Jun and works for fiscal years. If your using a calendar year then use only the MONTH function for the width argument in OFFSET.

[@Reyesnes]

Wow, this video is great. Thank you very much Leila for this great solution. Is it too much to ask if you can make another video about YTD calculation, but adding more complexity with conditionals. Something like this: Maybe you have that same table with the months horizontally but with a large list of KPIs or products vertically (a matrix table). But then you have another "summary sheet" where the YTD calculations for each of the KPIs must be performed as follows: - YTD LY vs YTD CY summary comparison. - Then a YoY based on both YTD. Additionally, in this same "summary sheet" there is a drop-down list in a cell with the "month-year" which indicates up to where you want to perform the YTD calculation for each KPI. The calculations must then be conditioned by: - a search based on the KPI name (you must search for a match of the KPI name from the summary sheet vs. the KPI name in the main table) - And conditioned according to the drop-down list where you select the range of the year to evaluate. Example: In the dropdown you have set the value "Aug-2023". This means that: - for YTD CY you have to calculate the equivalent sum from Jan-2023 to Aug-2023. - for YTD LY you have to calculate the equivalent sum from Jan-2022 to Aug-2022. - and the YoY % would be based on the difference of both YTD calculations.

[@44.7b.kaharaditya6]

Thanks , very useful formula

[@tumeenyamdorj378]

Hi Leila, this is very nice and helpful. Much appreciated. I am interresting if there is an option that excel can make dynamic grant chart schedule based on 12 hour shift fo a week?

[@drpivotdrtovip5352]

Nice idea using opffset. I would have used sumifs for year and month. Kudos! However, offset with an altered height or width gives a range already. Therefor you don't need to specify the end of the range. See $N$7:$O$7:O7 at 8:06.

[@LeilaGharani]

Thanks for the tip :) I'll check it out.

[@hylarion]

If you can't play with the -month() formula, you can instead use something based on -(mod(column(XY),12)+1) to generate a continuous cycle of values from 1 to 12 depending on the column... A bit more cumbersome, but it is more "universal" because based on positions and not on cell values.

[@kaaa3485]

Thank you so much.

[@weili3794]

Dear Leila, I have the answer by watching your offset video. Brilliant! But what if the month criteria is somewhere else. For example, if I type the month June in cell A18. How do I sum YTD (Jan-Jun) in cell A19?

[@kaaa3485]

Hi Leila, I have to do a training for my job. I just love your training, your video; they are very clear and very helpful. Please, let me know what software do you use to record yours? thank you so much.

[@LeilaGharani]

Hi - I use Camtasia to record the screen. A canon camera to record the head-shots (although I did use my iphone at the beginning...) All the best for your training :)

[@tha2irtalib343]

awesome , Thanks for the nice job.

[@LeilaGharani]

My pleasure. I'm happy you find the tutorial useful.

[@shaikhazher1627]

FANTASTIC

[@halimjsc7260]

Thank alot 👍👍👍

[@alexrosen8762]

Awesome! Thanks Leila :-)

[@LeilaGharani]

You're welcome Alex :)

[@planxlsm]

very good !

[@juanjajan7243]

Thanks a lot

[@paulwillems4281]

Hi @Leila, I summarise my large data set in a pivot table. Can I use a dynamic range to always show the last twelve months data in the pivot table?

[@miroslavjordanov4459]

Hi, Leila, is there a way offset formula sums every second column? for example: b7+d7+f7+...+December column Thank you very much for the answer! Best Regards Miroslav

[@agape13]

Excel Queen 👑. 🏆🥇

[@KgasS]

Thanks for this insight. This works only in excel (a bug?)as the offset function in other sheets (Google/Zoho/Libreoffice) will not work as they did not accept negative width/column in the offset function. To work in all, a positive width version is needed. The formula sum(offset(B4,0,-month(B3)+1,1,Month(B3))) for volume sum and SUMPRODUCT(offset(B4,0,-month(B3)+1,1,Month(B3))*offset(B5,0,-month(B3)+1,1,Month(B3)))/B13 for weighted price works and hope it is easy to figure out where the cells are referring.

[@user-bo6mn6ch2p]

Great 👍

[@chipgiii]

This is great stuff. I was curious as to whether anyone has a clean way of having cost that are amortized during a fiscal year, spread out monthly. So if the start date is January 2018, and the FY19 begins in March 2019 I see the last 10 payments in FY19 ending two months before FY19 is finished.

[@weili3794]

Dear Leila, cell B15 = SUM(OFFSET(B7,0,0,1,-MONTH(B6)):B7) for YTD. What if I have 2 rows of volumes to add, for example volume 1 & 2. Basically every month has 2 rows to add.

[@stuark0]

Hi Leila, A very helpful video. One thing that I don't understand though is why its necessary to include the second parameter in the SUM function (ie. :N7 in this case)? As I understand it, as the OFFSET returns a range of cells (including the cell from the same column as the formula) the second parameter in the SUM function is redundant. To illustrate, won't these formula return the same result? =SUM( OFFSET (N7, 0, 0, 1, -MONTH(N6)): N7) =SUM( OFFSET (N7, 0, 0, 1, -MONTH(N6)) In my tests, they return identical results. Am I missing something? Thanks

[@googlesheetautomation3573]

Mam if this data in column then which formula we used because I use this formula but not give answer. if you can make video on this then it is great help from you side

[@joebonasia1140]

Can you use the Offset function with a particular referencing function ‘like counta for example’ order to calculate quarterly sums or averages? As I am doing this now it only moves cell by cell I would like to group 3 cells together and then skip 3 cells to the following quarterly data. Is it possible to use offset for generating quarterly summaries from monthly data? Any help would be greatly appreciated :)

[@arkachatterjee6981]

We can use running count and if conditions......it will be more simple

[@ChiltonTurbo]

The month number is very useful, but how would you do it if you wanted to do this for a financial year? Without helper cells!

[@glassigast100]

When you have a pretty huge dataset, huge workbook, isn't it better to use hard coded helper cells rather than using additional formulas? From a performance wise perspective.

[@briankibias8686]

good one

[@LeilaGharani]

Thanks Brian.

[@yanchaobjerkvig6313]

I like your tutorial. Do you have one for how to use Subtotal on Sumifs?

[@LeilaGharani]

Thanks. Glad you like it. I have one on SUMIFS, I'll upload it next week. I'll look to add SUBTOTAL as well, although I prefer the (newer) AGGREGATE function - I'll make a note of these for future videos.

[@LeilaGharani]

Hi Yanchao - you can find the SUMIFS video here: kzbin.info/www/bejne/d4vYc4GNgs1qjK8

[@andrewcopley994]

Hi Leila, I am gradually getting through your videos but just wonder on this one why you keep the colon and second cell reference in the formula if you have already set the range using OFFSET? Surely this is unnecessary. I get the same result using (in your cell B16) =SUMPRODUCT(OFFSET(B7,0,0,1,-(MONTH(B6)))*OFFSET(B8,0,0,1,-(MONTH(B6))))/B15

[@tamerhegab1513]

Love you

[@markhake5648]

What screencast software do you use?

[@empathic_mimicry]

It's here

[@ryanrust9142]

How do you calculate the YTD in the monthly payslips

[@Saddam-Abdulameer]

شكرا

[@hattemghanoom9557]

Very smart

[@LeilaGharani]

Glad you like it :)

[@israfilgazi8071]

Hi, I am very big fan of you. I am completing all of your tutorials one by one. Thanks a lot for these videos. Could you give me all templates? PLEASE PLEASE PLEASE

[@LeilaGharani]

Hi Israfil - Thanks for the support. For most of the videos, I have the Excel workbooks included. I'll start to include them in for the missing videos....

[@israfilgazi8071]

Thanks. Happy to know that.

[@chandur5251]

Madam can you help with macros which includes pivot table creation with v lookup from external data in it.

[@gantulgaerdenechimeg3520]

Leila, have you ever heard rose diagram (used in geology)? Please show us how to do it please. @t Thanks in advance Your big fan

[@belaalhayajneh4796]

Hi mam, I made repor data in excel for invoices, and I saved the invoices as pdf by number of invoice, so how can I make a hyberlink between the new pdf invoices and the invoices number in report sheet, thanks

[@ericpeters3917]

I am doing a spreadsheet where I am deriving data from multiple worksheets based on multiple criteria for a Fiscal Year using drop down lists. I have been able to use SUMIFS in conjunction with INDEX & MATCH to get a total of a monthly columns for obligations based on the specific criteria (currently 3 different criteria). For the sum range in the SUMIFS I used my INDEX & MATCH which allows me to get the proper totals of the data by month (month is the column header) but I also need to get a cumulative sum (i.e. if I select Oct (1st month of FY) from the drop down I get October's total, when I select Nov from the drop down I get October + November. After watching this video I have been trying to figure out how to apply it to my problem but have been unsuccessful so far. The second worksheet that I am using the SUMIFS to calculate is an external connection to an Access database as well so the number of rows in it may periodically increase. Formula I used for my SUMIFS is =SUMIFS(INDEX(Table2[[Oct]:[Sep],0,MATCH($C$3,Table2[[#Headers],[Oct]:[Sep]],0)),Table2[FY],Table1[FY],Table2[Organization],Table1[Organization],Table2[Account],Table1[Account]). Can OFFSET be used to allow me to complete the YTD calculation in my case? One problem I am running into is that since the second worksheet is an import of Access the Oct-Sep headers are text not dates so the MONTH function doesn't work.

[@PONCEJE]

Send it to me and I'll fix it.

[@subhasisbhattacharjee9187]

Hi Leila, I have some set of data month wise quantity and selling price, I want to sumproduct the selling price at a particular column based on a month selected from drop-down list. Please help.

[@muhammadumar-bi7jf]

Dear good job But how to calculate 2 or 3 month average in one cell supposed January and February combined average. If we want 6 month average and one value is zero then answer #divd#?

[@nader6450]

Hi I'm building a dashboard for our sales over the year. I could do a dynamic chart to show a chart to show YTD compare with the same period last year, I need now to do the same dynamic chart to show current week with same last year + current month with the same last year. is there any way to make Excel calculate them automatically based on the date that we are in. Hope my question is clear. Thank you.

[@poilou2607]

Actualy you do 'nt need the second term of the sum (:Ox) because the offset is already a vector

[@mchllwoods]

U can use the columns function also instead of minus month.

[@LeilaGharani]

Very true. Thanks for the contribution :)

[@mchllwoods]

Leila Gharani no prob. Keep up the awesome work.

[@swastik9872]

cud pls explain how column function will reset to 1 at january

[@profbfc]

same question as Nilesh

[@JF-dy2dr]

=if(year(c6)=year(b6), c7+b7,c7) should do ytd volume, no? similar for calculating ytd avg price.

[@zeeshanrafi6511]

Hi greetings from India.

[@zeeshanrafi6511]

I would need help from u out of ur busy schedule as ur great and fabulous with ur excel skills, if u can respond It would make my work easy.

[@hosseinhosseinpoor4845]

سپاس

[@stephanietao546]

I have 10 years of WMT dividend data from 2010-2019. Walmart paid 4 times per year except for Y2017 (paid one additional at the end of the year), which means I have four rows for each year but 5 rows for 2017. I need to sum dividends paid for the first four rows of each year. I tried sumif() with offset() but that didn't exclude the 5th rows of 2017. Please advise, thank you very much.

[@kryzondaan1855]

I paused the video at 20 seconds in to see if I could figure this out ( first challenge ). I came up with 2 formulas, first not using array formulas. I did consider getting the month from the months but couldn't because I had made my own sheet and had formatted the months as text. =SUM(A$2:OFFSET(A$2,0,-MOD(COLUMN(A$2)-1,12))) where row 2 has the sales values, starting with Jan in column 1 ( A ) =SUM(INDIRECT("R2C" & MAX(($A$1:A$2="Jan")*COLUMN($A$1:A$2)),0):A$2) Then came up the above. This will work if there is a year without all 12 months listed, as long as it has figures for January. I beleive I had to use R1C1 reference for the indirect formula, because I needed to specify a column number ( I could have used "A1" references but converting a column number to a letter would have been horrific! ). The MAX function is basically looking at an array which is all months to date, and returns the latest column number where "Jan" occurs. Put these 2 in eg A4 and drag vertically. Now to come up with a formula which can cope with a missing "Jan"! I didn't bother with worksheet formulas when being tasked to develop some spreadsheets a few years ago and went straight with VBA for everything. Now I've been told I have to re-write some of the workbooks as .xlsx's if I can, so that other people can modify them later on ( good luck with that when some of the formulas are going to be very long ) I've been trying to get my head round the sorcery that is Arrays in excel, your videos have helped a lot so thank you for that. It certainly feels though that excel functions/formulas were not designed to do the things they can do and so lots of time we are looking for how we can do things using the limited tools we have available Edit: to come up with a formula for a missing Jan, can just use something like : =IF(COLUMN()=1,$A$10,IF(MONTH(A$8)>MONTH(OFFSET(A$8,0,-1)),OFFSET(A$12,0,-1)+A$10,A$10)), where row 8 contains the months, row 10 contains the values and row 12 going to display the YTD values. This is maybe a more intiuative way of solving the original problem anyway, but obviously doesn't use array formulas. I've got ideas for that but think I'll give up now

[@Ali_khan512]

Dear Leila I'm facing bit more confusing problem regarding my database wherein i want to calculate the payment of months which ia due but in a dynamic way. Pardon my mistake if i fail to elaborate my problem but i want it like if current month's payment is paid then the formula should avoid this month and calculte the rest month's of year due amount and if the the current month payment is not made then the formula should dynamically calculate the amount... I am positive that you'll reply soon

[@hemant5757]

Can you now do it with dynamic array function. I mean only put a formula in one cell and you need not to drag it till the last cell in the range?

[@eliasmiahsohel1845]

Madam, plz give dynamic 3y or 5y cagr calculation from 10y in excel video

[@pipo441]

How if you have want the data in column format, running totals YTD??

[@PHOK400]

what is the best way to do WTD, MTD and YTD sumproduct for an array of daily data

[@LeilaGharani]

That's an interesting one. I've added it to my list. Thanks for the suggestion.

[@sudheercherukuri14]

Hi Ms.Leila, I am new to Excel functions. I have a question in this example. Could you please calrify it? What if the table starts from March but not from January? In this case, width range as '-3' will cause an error. How can we hanlde this?

[@AtanasNenov]

Think you can use COLUMNS() function instead, then you wouldn't face this issue.

[@mifans9440]

متشکرم.

[@Thekastro911]

Leila Hi, is there a workbook for this video to practice?

[@LeilaGharani]

For this one, the workbook is included in the full course....

[@josephhorling6013]

Hi Leila, Can I download the whole workbook from you?

[@LeilaGharani]

Joseph, the complete workbook is included as working material in my Advanced Excel course.

[@egbesamuel]

When data range is vertical, pls what happens?

[@kaseox5436]

what if i have stock prices for stock prices for everyone day ?

[@ntheq3982]

Leila... I have a task that doesn't seem to be addressed in any KZbin example I've searched (50+ searches) and your Dynamic YTD is close but still not what I need. Maybe you can do a video on: "How to Find Cumulative Principal Paid on Amortized Loan at Every End-Of-Year" I have created a Mortgage Calculator with Amort Table that has columns with Period, Period Date based off start date, Payment, Principal, Interest and also column for Cumulative Principal & Cum Interest. Is there a FORMULA method to INDEX MATCH or VLOOKUP or DGET to find the DECEMBER Cumulative Principal data from the Mortgage Amortization Table. What makes this impossible for me is if the Loan Start Date is March 7, 2019 or October 23, 2019, it can't figure how to get the Cum Princ because 1) I need the Cum Princ for Dec which is only a few months in 1st year & 2) the row for the Cum Princ changes depending on the start date. I don't want to use helper cells. Wish you could see my workbook page. This is above my head. Thanks

[@bkkhatri2]

I this video.. One issue is that what if we are starting the report mid of the year say aug 2018 to oct 2020

[@Sam5597]

Month formula is only applicable when you're doing YTD as per Calender year. What if I have a strict Fiscal year say April to March? Seems Match needs to be introduced.

[@marmat7072]

Rahul Verma i have similar question.hoping to get an answer

[@mohamedaboobackersiddique7322]

@@marmat7072 i think u can add + 4 to -1 month

[@marmat7072]

Hi Leila, would this work on fiscal year, april is month one. I use the column function instead of month

[@marmat7072]

Thanks by the way

[@ajit555]

The month formula can be adjusted for Fiscal year.

[@mattgruber9573]

Trying to figure out how I can continue to use the YTD Volume - Dynamic Formula with the MONTH function, but have a fiscal year from Oct-Sept. The MONTH function only seems to operate 1-12 and can't be modified to reorder it to say...Month 1 = October (or whatever fiscal start you plug in). I have gone through the comments and seen similar questions, but only half answered solutions. I have tried the COLUMN function with success, but it does not recognize the new fiscal year and continues to sum into the next fiscal year. I have also tried the COUNTA and MOD functions, but can't seem to get it to work. If I use the COLUMN function, what does the formula look like for it to only count 12 and begin adding up the next fiscal year? Thanks for helping.

[@LeilaGharani]

Hi Matt, Here are the easiest ways I can think of right now: 1st method - use helper columns. So right above the months put index numbers, 1 to 12. 1 would be above Oct and 12 above Sep - january would be 4 etc... then use this formula =SUM(OFFSET(B7,0,0,1,-B5):B7) - assuming you have Oct in the B column and B5 is where the index number is (I've based the setup based on the example in the video). 2nd method: this doesn't require helper columns but it does require your months to be input as actual dates (like I have in the video) - the formula here would be =SUM(OFFSET(B7,0,0,1,-IF(MONTH(B6)>=10,MONTH(B6)-9,MONTH(B6)+3)):B7) - here I am making an adjustment. If month number is greater than or equal to 10 then we deduct 9 from it, otherwise we add 3 (because it means we're before October) - Hope this helps....

[@mattgruber9573]

Thanks Leila! Both solutions worked brilliantly for my situation. The "helper cell" method is easier, but after watching a lot of your videos; I am reluctant to rely on helper cells. The second method is definitely more complicated and beyond my current excel knowledge to figure out on my own. So I appreciate your guidance. Your videos and instructions are well thought out and very professional. Keep up the great work!

[@LeilaGharani]

Thank you Matt for the feedback. Glad you got it to work :)

[@Base2aus]

This works fine in the US but here in Australia our financial year runs jul - jun :( this method will not work, any suggestions

[@LeilaGharani]

I remember some options were mentioned in the comments. If you get a chance please scroll through them for ideas on how to handle different financial years.

[@ahmedelgazzar9530]

Thanks a lot for that amazing Video . I have some issues in excel and I need your support , Can You ?

[@LeilaGharani]

You're welcome Ahmed. Regarding the issues, it really depends on what they are an how much free time I get on my hands in the evenings. Feel free to send me your file (you can find my contact info on www.xelplus.com) with an explanation of the problem and the outcome...

[@ahmedelgazzar9530]

Sure I will do. Appreciate your understanding : )

[@abdanomer]

Great example of using offset. Could this example use the column() function instead of month (incase of the month written in text format ), then there should be a condition of counting 12 cells ! 😅 This could be more complicated, but it could be there if import the sales data from a software!

[@LeilaGharani]

Agree Abdelrahman - you'd have to use a workaround - either column(), counta() or even MOD() functions could be used to figure out the month....

[@firasmusmar2097]

what is the shortcut to make the range from specific sell to itself?

[@123rockstar2010]

Press the cell, and ":" (colon key)

[@sandhyasandy1965]

23/06 🏆🇮🇳 No Charges / FORM FILL UP, COPY PASTE work. Only WhatsApp this number 9917089859 OUR MISSION:- EMPLOYMENT INDIA🌞🌞

[@Prafullnu]

hi Leila - am adding total by date and need to update it at change of year - its within table with 40K = rows can you help?

[@LeilaGharani]

How about using sumifs between dates? Like the one shown here: kzbin.info/www/bejne/jnrLk39oZc-HfsU

[@Prafullnu]

Leila thanks for your revert I managed doing it using sumproduct

[@jyotiloomba464]

Yeah it's really helpful, but what if we have million of fixed assets with different put to use dates, residual value 5 %, depreciation rate 10%, if the life of an asset is less than 180 days from put to use date we will apply half depreciation for that financial year, if greater than 180 days then full dep for that FY, how to calculate book value for each year using straight line method

[@manishjindal9622]

Hi leila.. i have data (apr~mar) instead of (jan ~mar) how can i use month function

[@LeilaGharani]

Hi Mainsh - I've answered this somewhere. Not sure where...Might be in the comments below.....

[@manishjindal9622]

ya i got it

[@hassanalelaiw2240]

Hi Leila, How can we claculate (sum) rows and columns using dynamic YTD where the criteria for top row is months but for column would be more than one (years, regions & products) i.e what is the YTD sum of 2020 / AM where the current period is Mar (in other words, total sum for Jan 2021 to Mar 2021 for AM)? Year Region Product Jan Feb Mar Ap r ........ 2020 AM X1 50 723 125 ..... 2020 AM X2 132 70 235 2021 EU X3 565 1175 2020 AM X3 2021 EU 2021 EU 2022 AS Thanks for the excellent helpfull sessions

[@AbuTalha-eo7pr]

Really Congratulations to a great muslim educator named Leila GHANAMI ...

[@shahchoudhury5301]

Hi, need abit of help been searching everywhere but can't seem to find a solution. I am only trying to calculate negative values for each month can someone help.

[@jessicaykeith]

I believe the best way is to add an If statement >0, then Leylas' method. I hope this helps.

[@shahchoudhury5301]

@@jessicaykeith thank you

[@alvarolopez2353]

Does anyone have an idea of how to do this with weeks?

[@JF-dy2dr]

@Alvaro Lopez I imagine one could do something similar to what I did above for ytd or qtd with the weeknum formula to id when the week change

[@rksudera]

You look so very beautiful....

[@agapheputrasusilo741]

my brain hurts

[@gedenidzegiorgi7255]

[@ChiltonTurbo]

Stop saying Wolume