YTD Calculations by User/Month

DBB1

New Member
Joined
Mar 28, 2023
Messages
8
Office Version
  1. 365
I'm having problems trying to calculate YTD values for monthly employee data. My data is structured similar to this and spans several years.

Each Employee has individual values for any given month/year. The list of employees change as folks come and go, making it difficult to use Offset (this is a pretty large workbook with several pivots, slicers, data cube and VBA - it's already slow enough):

I'm trying to come up with a formula that would allow me to calculate YTD for the MonthTotal values for each employee. I've attached a screenshot of similarly structured data - (I apologize, but my organization will not allow me to install the XL2BB application).

Note that every employee has a monthly entry, resulting in several rows of duplicated "Period" values for each month-year. This has tripped me up when trying to use some of the other YTD calculations I've found, but I'm guessing I need to combine with some sort of Match on the Employee name. I've been pulling my hair out for weeks and have posted on other forums, but have had no takers.

Any ideas how to go about calculating the YTD for each employee? Any and all help is greatly appreciated:


1680037650449.png
 
It was just that the table you had posted was displaying as html code on the forum page. It seems to render properly with a subsequent login.
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
So, no way of getting that YTD info in the YTD column, per my original post? I need to add it to the underlying data model from there.
 
Upvote 0
So, no way of getting that YTD info in the YTD column, per my original post?
Hi, perhaps like this. If this isn't what you're looking for then it would be helpful if you reposted your sample data including the expected results for that column.

Book4
ABCDEF
1PeriodEmployeeDistrict1District2MonthTotalYTD
201/09/2022Jessica102102311251125
301/09/2022Mark65645865236523
401/09/2022Heather652240830603060
501/09/2022Jason69447511691169
601/10/2022Jessica56668412502375
701/10/2022Mark654258132359758
801/10/2022Heather9816425740610466
901/11/2022Jessica735125719924367
1001/11/2022Mark4834541502414782
1101/11/2022Heather5757125770018166
1201/12/2022Jessica588321238008167
1301/12/2022Mark2089614982224604
1401/12/2022Terri175696871871
1501/12/2022Heather3452573291821084
1601/01/2023Terri357542899899
1701/01/2023Jessica24423642604260
1801/01/2023Mark325542757525752
1901/01/2023Heather417812485418541
2001/02/2023Jessica6715461613210392
2101/02/2023Terry845256034053405
2201/02/2023Mark5215692621311965
2301/02/2023Heather2395854609314634
Sheet1
Cell Formulas
RangeFormula
F2:F23F2=SUMIFS($E$2:$E$23,$A$2:$A$23,">="&DATE(YEAR(A2),1,1),$A$2:$A$23,"<="&A2,$B$2:$B$23,B2)
 
Upvote 0
Solution
That did it!

Thanks to both of you for responding (and saving my neck)!
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top