Formula for automatically adding up year to date total for specific item

Dampland

Board Regular
Joined
Jul 25, 2014
Messages
85
https://imgur.com/a/LMZiMhH <------ Example image of the spreadsheet I use


Each week I pull sales data and add to a report. On the top of the report, I have cell designated that the user can type in the week of the year, and the spreadsheet will update to reflect the data for that particular week. That part is easy, just use index and match formulas (thanks to this forum help a few years ago) However I also have a column that shows the YTD totals based on the week selected.

Currently, each week when I add in the sales data, I have to go in to each and establish the range for the YTD sum for each item.

For example: 5 items are sold, so week 1 the YTD range to sum from is rows 1-5, but for week two it would be rows 1-10, week three is rows 1-15, etc.

Is there a faster way to do this? So formula I could just copy down, instead of updating the parameters for each new week. Also if an item sells zero units in a week, it does not show up in the sales data, so for that particular week it would show a zero for the YTD totals, because it wasn't in that weeks sales data.

Any way to speed up this process?

Thanks

Windows 10 PC, MS Excel 16


 
Last edited:

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
If your current YTD formulas on the sheet you've provided in the example image are only referencing the rows on that sheet, they're only going to include the values for the specified week and not going to include the data for the prior weeks.

However, could you change those formulas so that they directly reference where the data is being pulled from (e.g. Master_Data sheet?) and sum all weeks less than or equal to the nominated week?

For example:
=SUMIFS (master_data!Net Units Sold, master_data!SKU, $A4, master_data!Week_No, "<=" & $E$2 )

This will sum all the Net Units Sold on the Master_Data sheet where:

  1. the SKU on the Master_Data sheet matches the SKU on row 4 of your "2019 Sales Date for completed" sheet, AND
  2. the Week_No (assuming you have a column with this value in the Master_Data sheet) is less than or equal to the week No. in $E$2 of your "2019 Sales Date for completed" sheet.
 

Dampland

Board Regular
Joined
Jul 25, 2014
Messages
85
Thank you for your suggestion. I tried your formula just now, and it working "kind of". It is not summing all of the weeks (I selected Week 8, and for one of the skus I know that I had 1 unit in week 1, and 3 units in week 5, and nothing else this year. But the result showing is "3" .... so I need to mess around with this some more, but I really appreciate your suggestion. :)
 

Dampland

Board Regular
Joined
Jul 25, 2014
Messages
85
So I tried your suggestion on my YTD total Defective units column. (The report tab used in the screen shot in my first post is called "Financial Summary - Overview", and the tab with the weekly defective data is called "Defect-Returns by Week")

So my formula looks like this

=SUMIFS('Defect-Returns by Week'!H2:H5000,'Defect-Returns by Week'!C2:C5000,'Financial Summary - Overview'!A4,'Defect-Returns by Week'!B2:B5000,"<=" & 'Financial Summary - Overview'!$E$2)

Column H = Defective units for that week
Column C = SKU
Column B = Week


But for some reason, it is not correctly summing all of the data for my test sku. does something look wrong in my formula?
 
Last edited:

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
=SUMIFS('Defect-Returns by Week'!H2:H5000,'Defect-Returns by Week'!C2:C5000,'Financial Summary - Overview'!A4,'Defect-Returns by Week'!B2:B5000,"<=" & 'Financial Summary - Overview'!$E$2)
If this formula is in the very first data row of your 'Financial Summary - Overview' sheet (i.e. row 4) it may work, but not as you copy it down the table. You must 'lock' (make absolute with $) the sum range and the criteria ranges (as they are not changing relative to the formula's location otherwise the sum range will become a 'rolling window'. Try this:

=SUMIFS('Defect-Returns by Week'!H$2:H$5000,'Defect-Returns by Week'!$C$2:$C$5000,'Financial Summary - Overview'!$A4,'Defect-Returns by Week'!$B$2:$B$5000,"<=" & 'Financial Summary - Overview'!$E$2)

Otherwise the formula looks fine.
 

Dampland

Board Regular
Joined
Jul 25, 2014
Messages
85
Thanks .... yeah I realized I didn't have the absolutes in there at first. I changed it, but am still getting the error where it is not properly summing the total. so weird. Grrrrrrrrrrrrrrrr
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
Thanks .... yeah I realized I didn't have the absolutes in there at first. I changed it, but am still getting the error where it is not properly summing the total. so weird. Grrrrrrrrrrrrrrrr
I've had the same many times on other projects. SUMIFS is such a simple formula but you sometimes get gremlins in the ranges you're targetting and just can't spot the cunning little devils! :banghead:

Are you getting a result (but incorrect) or an error?

Debug ideas:
  1. Check that all the values being summed are in fact numbers and some are not text?Ensure that each criterion value and the data in corresponding criteria range are identical in type:
    • Is your "Week No." an integer (in both locations) or a date value?
  2. Do any of the targetted ranges inadvertently contain labels (e.g. for text criteria) or values that shouldn't be included in the calc.? (i.e. do your target ranges extend beyond where the "real" values are?)
  3. Try the formula with just one condition at a time to see if one of them is not working.
  4. Try "evaluating" the formula (Formulas / Formula Auditing / Evaluate Formula) to evaluate each component to check if the values at each stage make sense.
 

Dampland

Board Regular
Joined
Jul 25, 2014
Messages
85
Appreciate all of the suggestions. I will try all of them to see if it solves the issue.

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,858
Messages
5,465,104
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top