+ Reply to Thread
Results 1 to 3 of 3

Total items purchased within fiscal year using a dropdown.

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    Myrtle Beach, SC
    MS-Off Ver
    Excel 2013
    Posts
    21

    Total items purchased within fiscal year using a dropdown.

    Hello again,

    Overview:
    I have a spreadsheet with a list of inventory including the item name, item cost, and the date purchased.
    I'd like to total the item cost column based on the the fiscal year it was purchased. (Our fiscal year runs from July 1 - June 30).
    However, I'd like out finance team to be able to select the fiscal year from a dropdown in cell E3 from the "INVENTORY" sheet .

    Setup:
    Excel tab "ADMIN" contains a FISCALYEAR table as follows:
    -----A--------------B-----------C------------
    FISCALYEAR FROM TO
    2017 07/01/16 06/31/17
    2018 07/01/17 06/31/18
    2019 07/01/18 06/31/19

    Excel tab "INVENTORY" contains the inventory
    -----A--------------B-----------C-------------
    ITEM NAME COST PURCHASE DATE
    COMPUTER1 $900.00 01/13/17


    This tab also includes the dropdown in cell E3 (using data validation) to select the FISCAL YEAR (i.e., 2018, etc.) to total.

    Summary:
    Finance chooses the fiscal year from the drop-down (E3).
    Cell F3 would then display a total of all items purchased within that fiscal year.

    If there is an alternate idea, or easier way, I'm open for suggestions.
    Thanks!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Total items purchased within fiscal year using a dropdown.

    Try a formula like

    =SUMIFS(Inventory!B:B,Inventory!C:C,">=" & DATE(E3-1,7,1),Inventory!C:C,"<=" & DATE(E3,7,0))

    Though if your definition of Fiscal year 2018 is not July 1 2017 - June 30 2018, but is July 1 2018 - June 30 2019 then change to

    =SUMIFS(Inventory!B:B,Inventory!C:C,">=" & DATE(E3,7,1),Inventory!C:C,"<=" & DATE(E3 + 1,7,0))
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-13-2014
    Location
    Myrtle Beach, SC
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Total items purchased within fiscal year using a dropdown.

    Wow! You're amazing. The first option did exactly what I needed. (I was searching for SUMIFS and VLOOKUP). This was much easier.

+ 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. Changing Year Flag for Fiscal Year starting July
    By Michelle2468 in forum Excel General
    Replies: 1
    Last Post: 03-20-2017, 11:19 AM
  2. [SOLVED] Help with last year SUMIFS depending on what fiscal year week it is now
    By jmcole in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-11-2016, 07:02 PM
  3. Transpose Quarters from Calendar Year to Fiscal Year
    By jodiander in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-25-2013, 01:00 PM
  4. how to convert date to fiscal year if fiscal year start at 16th or 17th of July
    By sushil shakya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2013, 03:32 AM
  5. Running Total to date on Fiscal Year
    By rcolving in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-27-2010, 12:17 PM
  6. To automatically give fiscal period/fiscal year
    By Turnipboy in forum Excel General
    Replies: 7
    Last Post: 01-19-2006, 05:15 PM
  7. Fiscal year total from running 12 months
    By in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2005, 09:06 PM

Tags for this Thread

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