+ Reply to Thread
Results 1 to 5 of 5

Conditional Sum based on Date Range

  1. #1
    Registered User
    Join Date
    07-26-2014
    Location
    india
    MS-Off Ver
    2010
    Posts
    66

    Conditional Sum based on Date Range

    Dear All

    Here I have attached File containing 2 sheets
    One is for Report and another is for Data Entry.

    In Report Sheet Date range given in column H (H5 and H6)
    I want to make sum for B12 to B15 Item for date Range specified between H5 and H6
    Data is in Food Purchase sheet
    Suppose I want to make sum for Fruits for Date Range 01/08/2014 to 15/08/2014
    then output should come 32000/-
    it should not count fruit Fruit value of 16/08/2014 - 3000/- becuse it is out of date range

    Please help

    Atul
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Conditional Sum based on Date Range

    Try the below in cell I12 and copy down:

    =SUMIFS('Food Purchase'!D:D,'Food Purchase'!B:B,B12,'Food Purchase'!A:A,">="&$H$4,'Food Purchase'!A:A,"<="&$H$5)

    BSB.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Conditional Sum based on Date Range

    With an helpcolumn in column E to determine the criteria.

    After that a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    07-26-2014
    Location
    india
    MS-Off Ver
    2010
    Posts
    66

    Re: Conditional Sum based on Date Range

    Thanks Dude for your quick Reply

    Meanwhile I started to use following formula
    =SUMIFS('Food Purchase'!D9:D300,'Food Purchase'!B9:B300,"=" & Report!B12,'Food Purchase'!A9:A300,"<=" & Report!H5,'Food Purchase'!A9:A300, ">=" &Report!H4)

    But I found your formula is bit easier than my formula

    Thanks

  5. #5
    Registered User
    Join Date
    07-26-2014
    Location
    india
    MS-Off Ver
    2010
    Posts
    66

    Re: Conditional Sum based on Date Range

    Thanks for Reply oeldere

    But honestly speaking BadlySpelledBuoy has given easiest way to do that

    Atul

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional formatting dates based on date range and day
    By dancing-shadow in forum Excel General
    Replies: 4
    Last Post: 01-07-2014, 01:45 PM
  2. Replies: 8
    Last Post: 11-15-2011, 12:29 PM
  3. Excel 2007 : conditional format based on date range
    By Rob U Blind in forum Excel General
    Replies: 1
    Last Post: 10-21-2011, 09:41 AM
  4. Conditional format range based on date
    By sd7000 in forum Excel General
    Replies: 2
    Last Post: 09-08-2009, 03:53 PM
  5. Conditional formatting based on date range
    By RGB in forum Excel General
    Replies: 3
    Last Post: 05-23-2006, 12:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1