I am using SUMIFS to add amounts matching a given criteria between a range of 2 dates. The two week periods involved can sometimes cross the month boundary. Because my income/expenses bank account worksheet is set up on a monthly basis and the months are separated with spaces between each month, my SUMIFS formula contains 12 ranges (selected using Ctrl-Select) and I could not create a single name for the range.
Since the formula is common for each two week period, including the date range for the full year was the only way that I could see to address any two week period that needs to checked against dates that cross the monthly boundary. I'm trying to figure out if there is a way to use an array formula (Excel 2016) to simplify the SUMIFS formula and/or name the range rather than having 12 references to ranges in another worksheet in the workbook but I am drawing a blank. The formula is created in the master sheet which has only 3 rows in the range but in the active sheet, I insert the downloaded bank information each month so the formula needs to change the range boundaries when the bank information is inserted.
When I printed the formula for a single cell, it was 56 lines of code printing on an 8 1/2 x 11 sheet and there will be multiple cells containing similar formulas since I will be deducting expenses from the income (deposits) from the same two week period (another worksheet in the same workbook) then comparing the net income to the same 2 week period in the previous year (or the year before) in different workbooks. I'm looking for a way to simplify these huge SUMIFS formulae by finding a way to name the multiple ranges as a single name. Any other ideas?
Since the formula is common for each two week period, including the date range for the full year was the only way that I could see to address any two week period that needs to checked against dates that cross the monthly boundary. I'm trying to figure out if there is a way to use an array formula (Excel 2016) to simplify the SUMIFS formula and/or name the range rather than having 12 references to ranges in another worksheet in the workbook but I am drawing a blank. The formula is created in the master sheet which has only 3 rows in the range but in the active sheet, I insert the downloaded bank information each month so the formula needs to change the range boundaries when the bank information is inserted.
When I printed the formula for a single cell, it was 56 lines of code printing on an 8 1/2 x 11 sheet and there will be multiple cells containing similar formulas since I will be deducting expenses from the income (deposits) from the same two week period (another worksheet in the same workbook) then comparing the net income to the same 2 week period in the previous year (or the year before) in different workbooks. I'm looking for a way to simplify these huge SUMIFS formulae by finding a way to name the multiple ranges as a single name. Any other ideas?