Excel SUM Date Range Multiple Criteria
Hello all,
I have a little problem and perhaps someone could please help me? Assuming I have the values in Columns A as Start Date, B for End Date, then C for Quantity. Columns A & B is a Range per Row that gave me a Quantity in Column C.
Then, How would I get the value of Each Day Quantity?
Please see screenshot; please help with your formula to get the value of Column F-Daily Qty by Date Range.
Thank you so much in advance.
Excel1.JPG
Would 3/17 be 1, or would it be 7, or 7/4? Really, you should fill in the ??? with the values that you expect.
Bernie Deitrick
Excel MVP 2000-2010
Hi, thank for reply. ??? are the values generate from the formula. However, I don't know what should it be to get the value from the Sum of Column C. Example, manually, we can calculate Cell F15 as "7+10" or 17 by adding C4+C7; the criteria was E15 is 3/19,2015.
Do you know how to get for fill the answers automatically instead?
Thanks.
For Excel 2010, in F2, enter:
=SUMIFS(C:C,A:A,"<=" & E2,B:B,">=" & E2)
and copy down.
For Excel 2003, in F2, Enter:
=SUMPRODUCT($C$2:$C$100*($A$2:$A$100<=E2)*($B$2:$B$100>=E2))
Change the 100 to your last row of data.
PERFECT! Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks