+ Reply to Thread
Results 1 to 10 of 10

Using min and if formula I would like to get cost based off of the start and end date.

  1. #1
    Registered User
    Join Date
    07-27-2017
    Location
    HOUSTON
    MS-Off Ver
    2013
    Posts
    15

    Using min and if formula I would like to get cost based off of the start and end date.

    I would like to tell people in 2015 for this item(apple or orange or pears or grapes) we ate this much using column f and g.
    I would like to put this formula in Column d2

    Column A
    date
    10/1/2015
    9/1/2016
    9/3/2015
    9/3/2015
    9/3/2015
    2/23/2017

    Column B
    item
    APPLE
    ORANGE
    PEARS
    GRAPES
    APPLES
    PEARS

    Column C
    cost
    0
    20
    8
    7
    5
    10

    Column D
    MIN 2015
    =need formula here

    Column F
    START DATE
    1/1/2015

    Column G
    END DATE
    12/31/2015
    Last edited by David713; 07-27-2017 at 01:56 PM.

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    Hi David713, welcome to the forum! Perhaps this ARRAY FORMULA*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.


    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

  3. #3
    Registered User
    Join Date
    07-27-2017
    Location
    HOUSTON
    MS-Off Ver
    2013
    Posts
    15

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    No, this did not work.I do not have a column M:M.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,385

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    Yes you do! You said this:

    Column D
    MIN 2015
    MIN(IF($B:$B=$B3,$M:$M,FALSE))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    07-27-2017
    Location
    HOUSTON
    MS-Off Ver
    2013
    Posts
    15

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    Sorry for the confusion I just edited my original post. Where it says need formula here. This is where I need the formula.

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    Quote Originally Posted by David713 View Post
    No, this did not work.I do not have a column M:M.
    I took M:M from your original post, but you can change it. The important part is the conditional tests, highlighted in RED here:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Also, make sure you press CTRL+SHIFT+ENTER after you paste the formula in the Formula Bar.

  7. #7
    Registered User
    Join Date
    07-27-2017
    Location
    HOUSTON
    MS-Off Ver
    2013
    Posts
    15

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    This did not work. I also made sure I pressed CTRL+SHIFT+ENTER after I paste the formula in the Formula Bar. I am trying to find the minimum cost of each item in a specific year. For example:
    Apple minimum cost for the date range 1/01/2015 to 12-13-2015 is 0 because the date is 10/1/2015.
    Orange minimum cost for the date range 1/01/2015 to 12-13-2015 is 0 because the date is 9/01/2016.
    Pears minimum cost for the date range 1/01/2015 to 12-13-2015 is 8 because the date is 9/3/2015.

    Start Date:1/01/2015
    End Date:12/31/2015

    date item cost 2015
    10/1/2015 APPLE $0 =enter formula here
    9/1/2016 ORANGE $20 =enter formula here
    9/3/2015 PEARS $8 =enter formula here
    9/3/2015 GRAPES $7 =enter formula here
    9/3/2015 APPLES $5 =enter formula here
    2/23/2017 PEARS $10 =enter formula here
    Last edited by David713; 07-27-2017 at 02:46 PM.

  8. #8
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    Maybe this?:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    07-27-2017
    Location
    HOUSTON
    MS-Off Ver
    2013
    Posts
    15

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    You had the right idea. This ended up being the solution. =MIN(IF(($A:$A>=$F$2)*($A:$A<=$G$2)*($B:$B=$B2),$C:$C,FALSE))

  10. #10
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Using min and if formula I would like to get cost based off of the start and end date.

    Cool, glad you figured it out! If complete, please go to Thread Tools up top and mark your thread as SOLVED. Thanks!-Lee

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

+ 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. Replies: 2
    Last Post: 03-26-2016, 08:29 PM
  2. Replies: 2
    Last Post: 03-06-2016, 09:22 PM
  3. Replies: 2
    Last Post: 03-06-2016, 11:05 AM
  4. Replies: 3
    Last Post: 03-06-2016, 11:02 AM
  5. Replies: 2
    Last Post: 03-06-2016, 11:02 AM
  6. [SOLVED] Help in writing formula in excel to produce dates based on start date and end date
    By shoot for moon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2014, 01:00 PM
  7. Replies: 3
    Last Post: 10-23-2012, 09:04 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