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

[rahulsharma012]

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

 

[vijay.vizzu]

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

 

[rahulsharma012]

All i want is a combined formula in one cell.

 

[vijay.vizzu]

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

 

[rahulsharma012]

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.

 

[vijay.vizzu]

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

 

[WouterJ]

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

 

[vijay.vizzu]

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

 

[Faseeh]

Hi rahulsharma012,


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

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

Regards,

 

[rahulsharma012]

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

 

[Hui]

=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)

 

[rahulsharma012]

now thats called hiting the bulls eye


thanks a lot HUI