• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get sum of values based on dates [SOLVED]

rahulsharma012

New Member
I want to get sum of overtime values based on date criteria for may month only.below is the link.


http://www.fileconvoy.com/dfl.php?id=g9eb0fb36a6cf65a8999297153cfa3ccb0f36f3e1d
 
Dear RahulSharma


Why don't you use SUMIF function, i use this function in your data, it works fine


=SUMIF($B$2:$B$34,F2,$C$2:$C$34) by taking cell F2 for the date as a criteria


=SUMIF($B$2:$B$34,B2,$C$2:$C$34) by taking B2 as criteria and drag to down


If other than this requirement, please post clearly what is your requirement


Regards
 
Dear Rahul


You can also use Pivot Table to do this, Its very simple and quick solution


Please try it by putting the cursor on A1->insert->PivotTable


Regards
 
i mean based on employee for example "callum" and based on date criteria where date should be from 01/05/2011 to 31/05/2011 sum overtime values that fulfill this criteria.
 
Dear Rahul


Now i got your point, please let me work on that


meanwhile please see http://chandoo.org/wp/2010/06/24/between-formula-excel/ may it solved your prob


Regads
 
Hello Rahul,


You could try this:


Add in D2 the formula:

=MONTH(B2) and drag down.


Then in F2:


=SUMIFS($C$2:$C$34;$D$2:$D$34;$G$2;$A$2:$A$34;$H$2)


Where G2 is the month (number!) you want to select and H2 is the name of the employee.


Cheers,


Wouter
 
Dear Rahul Sharma

If you like Pivot Table then please try this

Select A1 and press INSERT--> PIVOT TABLE --> insert pivot table in existing sheet (i selected G1)


Employee as Row Lables

Overtime as Values Labels

Date as Row Labels


Select G3 and click Group Field in Pivot Table tools options

currently it will show starting at (DATE) and Ending at (DATE) and highlights month

Now click on Years, now both months and years will highlights then press ok button


Now you can see, 2011 in every field, select and right click and remove "years"


Now the data is ready (I will working on formula based) meanwhile you can try this


Regards

Vijay
 
Hi rahulsharma012,


Try this, you can change the DATE(2011,5,1) to your desire (desired month):


Code:
=SUMPRODUCT((MONTH(B2:B34)=MONTH(DATE(2011,5,1)))*(YEAR(B2:B34)=YEAR(DATE(2011,5,1)))*(C2:C34))


Regards,
 
sorry folks the above mentioned formula didnt work so i am again sending the file with the below mentioned link,with what i want is mentioned in excel file in a line.


i want either sumproduct or sumifs formula to do this.


http://www.fileconvoy.com/dfl.php?id=g728ec345c8f6039a999297768da11d098fb099bde


regards,

Rahul
 
=SUMIFS(C2:C34,A2:A34,"callum",B2:B34,">="&DATE(2011,5,1),B2:B34,"<="&DATE(2011,5,31))

or

=SUMPRODUCT((A2:A34="callum")*(B2:B34>=DATE(2011,5,1))*(B2:B34<=DATE(2011,5,31)),C2:C34)
 
Back
Top