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

[Dampland]

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

 

[Col Delane]

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]

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]

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?

 

[Col Delane]

=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]

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]

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!

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]

Appreciate all of the suggestions. I will try all of them to see if it solves the issue.

Thanks again.