Sum of quantities on monthly basis

[vkgupta]

I have data base of two columns i.e.

Date Pour

21-Sep 422.00

26-Sep 381.00

11-Oct 360.00

17-Oct 414.00

06-Nov 479.00

14-Nov 17.00

I need to sort it in following table:


Monthly Progress Total Pour = 6209.00

A B

Sep-2012 803.00

Oct-2012 774.00

Nov-2012 1059.00


Q: Someone pl help me in making a formula for column B figures. (Figures shown are calculated manually). I have only cpied a very small portion of the table, though it is huge.

 

[bobhc]

Good day vkgupta


You could either turn your data in to a table filter on months and do a sum on pour amounts or better still after you have turned your data into a table turn the table in to a pivot table

 

[vkgupta]

table 2 need a formula to give the instant numbers if any changes made in data entry in table 1. Adding a row in table 1 can be possibility for to monthly sum and simply forwarding to table 2. But I would rather prefer a formula.

 

[Debraj]

Hi Guptaji..


If your data is in below format.. and you have converted the area as TABLE..

[pre]

DATE	POUR_Area
21-Sep	422
26-Sep	381
11-Oct	360
17-Oct	414
06-Nov	479
14-Nov	17[/pre]
then..

Use below two formula's to get the required information..


In E2

[code]=IFERROR(INDEX(TEXT(Table1[DATE_Area],"mmm-yy"),MATCH(0,COUNTIF($E$1:E1,TEXT(Table1[DATE_Area],"mmm-yy")),0)),"")


In F2

=IF(E2<>"",SUM(IF(TEXT(Table1[DATE_Area],"mmm-yy")=E2,Table1[POUR_Area])),"")

Both formula needs Ctrl+Shift+Enter for formula confirmation..


Can you please check if attached is acceptable..


https://dl.dropbox.com/u/78831150/Excel/Sum%20of%20quantities%20on%20monthly%20basis%20%28vkgupta%29.xlsx[/code]


BTW..

is the below is TYPO or it should be..

Nov-2012 1059.00

Regards,

Deb

 

[vkgupta]

I have tried like this so far:


Month (Column C) Pour (Column D)

Sept 2012 =sumif(A4:A100,C4,B4:B100)


where:

Column A is the date column in table 1

Column B is the Pour quantity Column in table 1


Column C is MOnth Column in table 2

Column D is Pour Column in table 2


I think the problem with this function is that the function is looking for a set criteria i.e. Sep 2012 which actually excel reads as September 1, 2012 and since column A (Date Column) has other dates then Sep 1, 2012 it results to 0.


So if this function can have an another function which converts the criteria to Month instead of a date then it will surely work.


I am working on your above suggestion and let you know the results..

 

[bobhc]

vkgupta


Tables are dynamic, any data entered in them would be in the pivot table on refresh, in the pivot filter how you want and display sum/average/max/min or any other that you want or need

 

[vkgupta]

Pivot table has helped by putting date in row header and pour in pivot values selected for sum and then group date columns to month. But I have to watch if it is getteing updated by itself when new data entry is made.


Thanks for the help dear.

 

[vkgupta]

Just checked it has to be refreshed in order to get latest results.


Let me know how can I do an automatic update setting in this Pivot Table.

 

[vkgupta]

Forgot to write right TAGS and help Chandoo link


http://chandoo.org/wp/2009/11/17/group-dates-in-pivot-tables/