Dear Can any one guide me for my Problem in Following formula
=SUM(SUMIFS(RepData[InWt],RepData[LOC],A10,RepData[Order No],H7,RepData[Ecode],I7,RepData[Kt],J7,RepData[Metal],K7,RepData[InDate],"<"&F7,RepData[OutDate],{">=41961","=0"}))
If I use Out Date in Numeric then its working fine but I want to use Cell range instead of Numeric Date
thanks in advance
Try
=SUMPRODUCT(SUMIFS(RepData[InWt],RepData[LOC],A10,RepData[Order No],H7,RepData[Ecode],I7,RepData[Kt],J7,RepData[Metal],K7,RepData[InDate],"<"&F7,RepData[OutDate],A1:B1))
A1 = >=41961
B1 = 0
Try this instead...
=SUM(SUMIFS(RepData[InWt],RepData[LOC],A10,RepData[Order No],H7,RepData[Ecode],I7,RepData[Kt],J7,RepData[Metal],K7,RepData[InDate],"<"&F7,RepData[OutDate],">=11/18/2014",0))
However, I dont see how this will work, you are asking if a specific date is = 0?
Perhaps if you upload a sample workbook, it would be easier to help?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Just to follow up on Jonmo's solution, if you don't want to have to type the actual operator (">=", etc.) in the cell, and just use e.g. 41961 in A1 and 0 in B1:
=SUMPRODUCT(SUMIFS(RepData[InWt],RepData[LOC],A10,RepData[Order No],H7,RepData[Ecode],I7,RepData[Kt],J7,RepData[Metal],K7,RepData[InDate],"<"&F7,RepData[OutDate],{">=","="}&A1:B1))
though his solution is arguably preferable if these operators are subject to change (one week less-than, the next greater-than, etc.)
Regards
Thank this is working perfectly as I want
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks