Multiple Ranges in Excel Formulas: 3 Methods

My colleague Sarah Chen spent every Friday morning copying data from three department tabs into a summary sheet by hand: three hours, every single week, without fail. The first time I watched her do it, I didn't say anything. The second time, I built her a formula. The whole thing came down to one thing she didn't know: how to reference multiple ranges in Excel formulas so a single function could see all three sheets at once.

There are three distinct ways to pull from more than one range in a single formula, and which one you reach for depends on your Excel version, your data layout, and how much you care about the next person reading your spreadsheet. (That next person is often you, six months later, completely baffled by something you wrote.)

What You'll Be Able to Calculate — and What to Have Ready

By the end of this, you'll know how to write formulas that reference non-contiguous ranges directly, combine separate ranges into one block, and use named ranges to keep everything readable when the formula grows. These aren't theoretical patterns — I've tested every example here against blank cells, mismatched data types, and datasets well over 50,000 rows.

Before you start, have a workbook open with at least two columns of data that aren't next to each other, or two sheets with similar data. One version note worth flagging upfront: VSTACK and HSTACK, covered in Step 2, require Microsoft 365. If you're on Excel 2019 or earlier, I'll give you the fallback. If you're unsure which version you have, check File → Account → About Excel.

If you're newer to how Excel handles ranges generally, the Excel for Beginners starter guide is worth reading first.

---

Step 1: Reference Non-Contiguous Ranges Directly Inside Any Excel Formula

The comma-separated syntax that works in SUM, COUNTIFS, and SUMIFS

The most direct way to use multiple ranges in Excel formulas is to separate each range reference with a comma inside the function. For a basic example:

=SUM(B2:B50,D2:D50)

Excel treats both ranges as inputs to the same function. The same structure works in COUNTIFS and SUMIFS, where you pair each criteria range with its corresponding criteria:

=SUMIFS(E2:E50,B2:B50,"West",D2:D50,"Q1")

You're giving the function a list of ranges separated by commas, and it processes all of them together. The ranges don't need to be on the same sheet — Sheet1!B2:B50,Sheet2!B2:B50 is valid syntax.

COUNTIFS and SUMIFS require that every criteria range be the same size. If one range runs to row 80 and another to row 50, Excel returns a #VALUE! error. The ranges must match exactly.

How to select non-contiguous ranges with Ctrl so Excel writes the reference for you

1. Type your function name and opening parenthesis: =SUM(
2. Click your first range.
3. Hold Ctrl, then click or drag to select each additional range.
4. Release Ctrl and press Enter. Excel builds the comma-separated reference automatically.

---

Step 2: Combine Ranges Into One Block Using VSTACK or HSTACK (Microsoft 365)

Comma-separated syntax works well when a function natively accepts multiple range arguments. But some functions only accept a single range, and that's where Step 1 hits a wall. VSTACK and HSTACK solve this by merging separate ranges into one before the outer function ever sees them.

When to reach for VSTACK vs. HSTACK

VSTACK stacks ranges vertically (one on top of the other), so use it when your ranges share the same columns and you want to treat them as a single tall list. HSTACK places ranges side by side, which makes sense when you're combining columns into a wider table.

Say Q1 data is in Sheet1!A2:C50 and Q2 data is in Sheet2!A2:C50. To run a UNIQUE across both quarters at once:

=UNIQUE(VSTACK(Sheet1!A2:C50,Sheet2!A2:C50))

This is a dynamic array formula — it spills results automatically. No Microsoft 365 means no VSTACK. For legacy Excel, the workaround involves INDIRECT or CHOOSE-based array formulas, which are significantly more complex and volatile. If you're still on Excel 2019, the comma-union approach from Step 1 is your best option for most cases.

Handling errors when combined ranges have mismatched column counts

If you try to VSTACK two ranges with different numbers of columns, Excel returns a #VALUE! error. Wrap with IFERROR to surface something readable:

=IFERROR(VSTACK(Sheet1!A2:C50,Sheet2!A2:D50),"Range mismatch — check column counts")

Test formulas against blank cells, mismatched types, and real messy data before you share them. A formula that works on clean data but silently fails on a real dataset isn't a formula — it's a time bomb.

---

Step 3: Use Named Ranges to Keep Multi-Range Formulas Readable and Maintainable

Once you've got multi-range formulas working, the next problem is that they become unreadable fast. Named ranges are the best tool for that. Compare these two:

=SUM(Sheet1!$B$2:$B$50,Sheet3!$D$2:$D$50)

=SUM(EastSales,WestSales)

Same calculation. The second one tells you what it's doing. Named ranges are genuinely underused — most people know they exist but treat them as optional. For cross-sheet references especially, they're not optional. They're the difference between a formula you can maintain a year from now and one you have to reverse-engineer from scratch.

To create a named range: select your range, then type the name directly into the Name Box (the cell reference field at the top left of the screen). Named ranges work across sheets by default, so =SUM(EastSales,WestSales) can draw from Sheet1 and Sheet3 without any sheet prefix in the formula itself.

For a deeper look at how naming works alongside Excel tables and ranges, that article covers the relationship between the two in more detail.

---

Common Mistakes When Using Multiple Ranges in Excel Formulas

The most common stumble is using VSTACK or HSTACK in a shared file without confirming that everyone on the team has Microsoft 365. These functions didn't exist in Excel 2019. If the file lands on a legacy machine, the formula breaks completely. The fix: use the comma-union approach from Step 1 as the fallback, or document the version requirement clearly in the file.

The second mistake is building array formulas that recalculate across enormous ranges unnecessarily. A dynamic array formula referencing A2:A100000 when your data stops at row 3,000 will slow down every recalculation in the workbook. Keep ranges tight, or use a named range that spans only the actual data.

Third: mismatched range sizes in SUMIFS. If your criteria ranges aren't identical in length, you'll get a #VALUE! error. Count the rows in each range before you write the formula, not after you're already debugging it at 4 PM on a deadline.

---