How to calculate data by month.

[ShoutMercy]

Good morning,

I once again seek the assistance of this forum (I swear you're not doing my job for me). Thanks to your help in completing my last project I am now looked upon as some sort of Excel guru...if only they knew the truth

Please see the attached spreadsheet.

Basically, several depots through the country receive regular deliveries of a product. Depending on usage they can get several deliveries of stock per month. What I am trying to achieve is when entering a delivery date and the amount of product delivered it shows a summarised version on another sheet.

So Sheet 1 shows a list of dates and the volume of product delivered. Sheet 2 should just show Month (A2 to L2) and the total delivered in that month under the appropriate column. This data will be ever changing as more deliveries are made throughout the year.

I tried using the IFS command as it worked previously for me but there was less data to work with that time and therefore less confusing.

My thanks in advance for any assistance.

[Pepe Le Mokko]

Hi
why not use a simple Pivot Table ?
I changed your data layout a bit and added a PT on sheet2
(I made your data an Excel Table

[AliGW]

I've converted the month names to proper dates with custom formatting (first of the month with "mmm" as the format), then used this:

=SUMIFS(Sheet1!$B$3:$B$10,Sheet1!$A$3:$A$10,">="&A$2,Sheet1!$A$3:$A$10,"<"&EDATE(A$2,1))

I swear you're not doing my job for me

As long as you can explain it all to the powers that be ...

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[ShoutMercy]

Thank you Pepe and AliGW.

I couldn't get your attachment to download AliGW, but I got the jist of your comment and formula and got it working.

Thanks you very much :D

[Pepe Le Mokko]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[AliGW]

I couldn't get your attachment to download AliGW

Weird - that's NEVER happened before. Something at your end, I'm afraid. It was just your own workbook with my solution applied.

Please follow Pepe's instructions to mark the thread as solved, etc. Thanks.

[ShoutMercy]

Not solved yet

Please see the attached spreadsheet.

Apparently manually entering the info is too much of a task for some

As you can see I had a go using the IFS instruction again. The information on the Delivery tab will be copied and pasted from a report, it will follow the same layout every time. What I was hoping would happen was that the information in the Summary tab would auto-populate so that it would show the total value of the product under each period (1 - 12) respectively. I have the IFS formula working fine on a very similar spreadsheet and assumed that changing the formula to match the new sheet names would be a doddle. More fool me.

Again, any help is much appreciated.

[ShoutMercy]

Forget that last post. About 30 seconds after posting i realised the error of my formula.

Consider it solved