Create a single name for multiple selected ranges (Excel 2016).

rappleby

New Member
Joined
Sep 23, 2011
Messages
11
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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,694
Office Version
  1. 365
Platform
  1. Windows
Without seeing a (simplified) example of the sheet layout and the existing formula, I doubt that anyone would even attempt to answer this.

Arrays can not be used in SUMIFS ranges, so what you are asking is not possible in the way that you are thinking, there might be some alternative ways that would work but the information that we have so far is open to a lot of misinterpretation.
 

rappleby

New Member
Joined
Sep 23, 2011
Messages
11
Without seeing a (simplified) example of the sheet layout and the existing formula, I doubt that anyone would even attempt to answer this.

Arrays can not be used in SUMIFS ranges, so what you are asking is not possible in the way that you are thinking, there might be some alternative ways that would work but the information that we have so far is open to a lot of misinterpretation.
Thanks for replying, Jason. Is it possible to concatenate a series of ranges into 1 range which I can then name and use a single reference?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,694
Office Version
  1. 365
Platform
  1. Windows
You can apply a name to just about anything, but the problem will be getting a formula to accept it. Defining multiple ranges as a single name doesn't mean that you can then use that in any formula as if it was a single range, the formula will still see the individual ranges as if you had tried to enter them all into the formula without using a named range.

As I said, there may be something that will work but without an example of the layout and the formula / criteria arrangement it would be impossible to come up with anything. Preferably an XL2BB mini sheet (see link in my signature) consisting of fictional data that is an accurate representation of your actual sheet (copy a small section, change any personal details or delete them if not relevant to the formula, use cartoon characters as payee names, etc).

Examples of things that we need to be able to see from the sheet would be:-
-Are the ranges that you refer to in 1 single column with rows to ignore or in multiple columns (or even multiple sheets)?
-How are the criteria ranges positioned relative to the sum ranges?
-If dates are being used as criteria, are they in numeric or text format? (numeric dates will change to a 5 digit number if you select the cell and press Shift Ctrl ~ )
 

Watch MrExcel Video

Forum statistics

Threads
1,130,333
Messages
5,641,550
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top