Help with YTD fomula for Sales

[Des_Ireland]

Hey all,

Long time reader - first time poster!

So I have a simple workbook which has a sheet containing sales records by year, month and team\dept. Version below.
It has a totals sheet and a lists page for some input validation.
On the "Sales Total" sheet, I want to present the users with some dropdowns to vary the totals they see. So the user can select year and month, the result being the total for sales that month and then Sales YTD for that year (up to month end). I'll eventually incorporate being able to reduce it down to sales by team (showing MTD and YTD)
I can easily get the month total, given the month and year, but i'm struggling with the YTD figure. Can anyone help? I've tried all sorts of SUM, SUMIF, SUMIFS, sumproduct, etc using index, match, address too, but can't seem to figure it out!!

Any help would be greatly appreciated!!

Year	Month	Team	Sale amount
2015	December	IT	100
2016	January	consult	50
2016	January	Service	10
2016	February	IT	25
2016	March	support	5

<tbody>
</tbody>

 

[GJSBED]

Have you considered a pivot table?
Utilize Filters (pre 2010 excel)/Slicers(2010version and later) to do your Year and Month options, and to allow the reader to drill down into the numbers by team that way.

 

[Des_Ireland]

Unfortunately, a pivot won't suffice here for me. This is something that I need to make as simple as possible and lock down ranges etc and my experience of giving Pivots out for people to use hasn't been great so far.
Thanks for the suggestion though!

 

[JackDanIce]

Make the file read-only or limit accessibility by using passwords?

Is the information extracted from the data more important or providing a delayed, but idiot-proof spreadsheet priority here?

 

[Des_Ireland]

its a 50/50 between whats more important - both the information and being idiot proof is whats needed.
Where the sales are entered will be locked so only certain people can update this list, with the overall file needing a password to open due to security standards.. I'll be locking down formulas too, so essentially all a normal user can do is change the year and month dropdowns.
sometimes the sales get updated after a month end too, so the list of sales won't always be in month order.

 

[JackDanIce]

Seems trivial, Excel has capability to do exactly what you need but you're trying to redesign the wheel.

Get the end users trained up and make them accountable for mistakes if they can't learn how to use a pivot table.

Or build a "reset" feature (VBA) to rebuild the pivot table if they mess it up.

Request the user backs up the file before making any changes to it.

You're already saying the file itself needs a password to open and you're locking down the formulas - open it as read-only for those who only need to extract data (not update it) and for those who do need to update it.. caveat emptor or buyer beware if they refuse to train themselves up to use a tool thats been created to aid them.

 

[JackDanIce]

Two links to limiting changes to a pivot table:
How to Restrict Users from Changing or Modifying a Pivot Table | Jet Reports Knowledge Base
Prevent Changes to Pivot Table Setup - Excel Pivot TablesExcel Pivot Tables

 

[Des_Ireland]

I appreciate the advise on going with a better solution with regards the actual file, but for various reasons it won't work. I'd love it if people would go to the bother and actually learn a bit about excel, but i've no control over that. To get them trained etc is a medium term solution.

I really just need to get a formula to calculate the figure. If its impossible to do so, then fair enough - another solution will be needed! (thats someone elses problem then!)

 

[JackDanIce]

It's already someone else's problem if you ask me! It sounds so unnecessary what you're trying to do but good luck!

This is one more link that uses SUMPRODUCT function to "Filter" & "Sum", in effect SUMIF, that may direct you to a solution you're after:
Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online

 

[FormR]

Hi, how about something like this:

Excel 2013
	A	B	C	D	E	F	G	H	I
1	Year	Month	Helper (can be hidden)	Team	Sale amount				YTD
2	2015	December	01/12/2015	IT	100		2016	February	85
3	2016	January	01/01/2016	consult	50
4	2016	January	01/01/2016	Service	10
5	2016	February	01/02/2016	IT	25
6	2016	March	01/03/2016	support	5
Database

Cell Formulas
Range	Formula
C2	=(1&B2&A2)+0
I2	=SUMIFS(E:E,C:C,">="&G2&"-1-1",C:C,"<="&EOMONTH("1"&H2&G2,0))