Date Formula

[VHaig]

I've got a sheet (Sales) that records date, client, invoice no. amount, VAT and total. This is populated every time a client is invoiced.

On another sheet (Sales by Month & Year) I have financial year, month (number), month (text) and amount invoiced.

The information on this sheet is then turned into a pivot table so I can compare sales figures for each month in the financial period. At the moment I'm using autocalculate to find out the total sales figures for each month and then I manually enter that number on the Sales by Month & Year sheet.

Is there a way I can have a formula which looks at the data on the sales sheet and puts the total figure into the correct cell onto the Sales by Month and Year sheet so that it happens automatically? I would like a formula to start in cell D2 on the Sales by Month and Year sheet.

 

[Villalobos]

Hi,

I see in your file the "Month no." and "Month" is not corresponding each other. "Month no." or "Month"should be considered in the calculation?

 

[VHaig]

Hi,

I see in your file the "Month no." and "Month" is not corresponding each other. "Month no." or "Month"should be considered in the calculation?

The number corresponds to the month in the financial year. For example the financial period is June-May, so June is month no. 1 etc

 

[Awesome Wally]

If you aren't going to reference the Fiscal Month Number I wouldn't even worry about creating a table for it. I would add a formula to your primary data on the Sales worksheet for Year and Month Name and then do your pivot. No reason to have another sheet total everything up. See attached example:

 

[VHaig]

If you aren't going to reference the Fiscal Month Number I wouldn't even worry about creating a table for it. I would add a formula to your primary data on the Sales worksheet for Year and Month Name and then do your pivot. No reason to have another sheet total everything up. See attached example:

Thanks for that, however, I need the months of the year to start in June through to March as that is when the financial period is. Doing it the way you mention puts them Jan-Dec. Was hoping for a formula to make this spreadsheet easy for the user.

 

[Awesome Wally]

You could sort the pivot table to the June-May view and it would stay that way upon refresh and additional data.

 

[Awesome Wally]

However if you really want to use another worksheet and the layout you had in the original post you can use: =SUMPRODUCT(--(TEXT(SALES!$A$2:$A$41,"MMM")=C2),--(YEAR(SALES!$A$2:$A$41)=A2),SALES!$F$2:$F$41) in Cell D2 of the Sales By Month Worksheet. ***But you will need to change your Financial Year to a single year (i.e. 2015 instead of 2014-2015). Attached as well.

 

[Villalobos]

The number corresponds to the month in the financial year. For example the financial period is June-May, so June is month no. 1 etc

Could you please check out this proposal? The date format is separated by slash (/) on my computer, if you have another character then change it in the DATEVALUE function.

 

[Awesome Wally]

That will work for your application