I am building a dashboard to summarize purchase data. I have a worksheet for purchase data, there is a reference tables worksheet with the start and end dates for fiscal months, with the associated fiscal year, and fiscal month (summarized below), and the dashboard worksheet.
Table: oetFiscalMonths
Start Date | End Date | Month | Year
10/1/17 | 10/30/17 | October | 2018
10/31/17 | 11/1/17 | November | 2018
On the dashboard there is a table of data formatted as below
| Beginning Budget | Total Purchases | Remaining Budget |
October | $XXXXXXXXX.00 | $XXXXXX.00 | $XXXXXXXXXX.00 |
November | $XXXXXXXXX.00 | $XXXXXX.00 | $XXXXXXXXXX.00 |
December | $XXXXXXXXX.00 | $XXXXXX.00 | $XXXXXXXXXX.00 |
January | $XXXXXXXXX.00 | $XXXXXX.00 | $XXXXXXXXXX.00 |
The formula I am looking to enter would go in the Total purchases, and would sum all purchases made in the fiscal month on the right, based on the table for fiscal months, while meeting several other criteria, which I have been able to complete with the SUMIFS
formula. I am just not sure how to handle the date range portion as they do not follow the calendar months even remotely mostly, and the table for the dates is formatted the way it is as several other portions utilize its current format.
Any help is greatly appreciated!