# Total items purchased within fiscal year using a dropdown.

1. ## 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. ## 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))

3. ## 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.

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