To combine corresponding inventory measures in Excel for Mac, add matching numeric arrays with +. If asset rows differ in order, look up each asset first, then add its returned quantities. This preserves separate results for each measure.

This walkthrough uses synthetic art assets with two measures: print copies and swatch cards. The task is to combine quantities from two storage locations while keeping each asset and measure identifiable. All quantities are illustrative.

Choose the formula by how the assets correspond

Before writing a formula, establish what makes two values correspond. Matching positions work only when both input blocks have the same asset order and measure order. Matching asset labels allows the source rows to appear in different orders.

Input condition Supported approach Result
Same assets, same row order, same measure columns Add equal-sized numeric ranges One result per asset and measure
Unique asset labels, different row order Add two multi-column XLOOKUP results One asset’s measures across separate cells
Repeated asset labels that should be combined Use a separate SUMIF calculation for each measure One result per requested asset and measure

The examples below derive these approaches from Microsoft’s documented array calculations, multi-item lookup returns, and conditional summation. Choose the method that matches your input structure before copying a formula.

Confirm your Mac edition

The lookup example applies to Excel for Microsoft 365 for Mac, Excel 2024 for Mac, and Excel 2021 for Mac. Microsoft explicitly states that XLOOKUP is unavailable in Excel 2016 and Excel 2019, despite those editions appearing in the page’s broad applicability list. Microsoft’s XLOOKUP documentation.

On a Mac, an Office application’s application menu contains its About dialog, which shows the version and license type. In Excel, check Excel → About Excel. Identify the product edition as well as the version number before selecting a formula. About Office: What version of Office am I using?.

The optional HSTACK formula later in this article requires Excel for Microsoft 365 for Mac or Excel 2024 for Mac; Microsoft does not list Excel 2021 for Mac for that function. The main calculations do not require it. Microsoft’s HSTACK documentation.

Set up two small art-asset inventories

Use a blank worksheet with ordinary cell ranges. Enter the first location’s inventory in A1:C4:

Asset Print copies Swatch cards
Tile-A 8 3
Tile-B 5 7
Tile-C 6 2

Enter the second location’s inventory in E1:G4, leaving column D empty:

Asset Print copies Swatch cards
Tile-A 4 2
Tile-B 9 1
Tile-C 3 5

Both numeric blocks are three rows tall and two columns wide. Their first numeric column counts print copies; their second counts swatch cards. The labels sit outside the arithmetic ranges.

In J1:L4, prepare the output area below. Enter only the headers and asset labels initially; leave the quantity cells empty.

Asset Combined print copies Combined swatch cards
Tile-A
Tile-B
Tile-C

For these examples, every quantity is a number and every asset appears once in each source. A missing record and a recorded zero have different meanings: use zero when the quantity is known to be zero, and resolve an absent record before treating it as zero.

Add aligned arrays to preserve every corresponding result

With the source rows in the order shown, enter this complete worksheet formula in K2:

=B2:C4+F2:G4

Confirm the formula with Return. In dynamic-array Excel, the output occupies K2:L4: three asset rows and two measure columns. Microsoft documents that array formulas can return multiple results and that dynamic results spill into neighboring cells. Guidelines and examples of array formulas.

The expected output is:

Asset Combined print copies Combined swatch cards
Tile-A 12 5
Tile-B 14 8
Tile-C 9 7

The calculation follows positions: B2+F2 produces 12, while C2+G2 produces 5. Each remaining position follows the same rule. The two source blocks become one result block with the same dimensions.

For a one-column inventory, the equivalent formula would be =B2:B4+F2:F4. It returns three quantities, one per row. The two-column version simply preserves a second measure alongside the first.

Why an outer SUM produces the wrong shape

These formulas calculate a grand total:

=SUM(B2:C4,F2:G4)

=SUM(B2:C4+F2:G4)

For this sample, each returns 55. That number mixes all three assets and both measures. Microsoft documents that SUM adds the values supplied as numbers, references, or ranges; wrapping the result array in SUM therefore reduces it to one value. Microsoft’s SUM documentation.

Use the six-cell result above as the acceptance check. A single cell containing 55 answers a different task, even though its arithmetic is correct.

A version-independent layout for aligned rows

If you prefer individual cell formulas, enter =B2+F2 in K2. Drag its fill handle right into L2, then fill K2:L2 down through row 4. Relative references adjust to calculate each corresponding pair. Microsoft documents this fill-handle behavior for Excel for Mac. Use Excel as your calculator in Excel for Mac.

This produces the same six quantities without a spilled result. It still depends on matching source row order.

Match asset labels when source rows are in different orders

When row positions cannot be trusted, clear the previous result from K2 first. Keep J2:J4 as the requested asset list. Enter the following in K2:

=XLOOKUP($J2,$A$2:$A$4,$B$2:$C$4)+XLOOKUP($J2,$E$2:$E$4,$F$2:$G$4)

Each lookup finds the asset in J2 and returns its two quantity columns. Adding the returned rows produces 12 in K2 and 5 in L2. Microsoft documents both exact matching by default and returning multiple items from a multi-column return range. Microsoft’s XLOOKUP documentation.

Copy the formula from K2 to K3:K4, leaving L2:L4 clear for the horizontal results. The dollar signs hold the source ranges fixed; the requested asset changes with each output row.

As a diagnostic, rearrange the second source’s complete records so that Tile-C comes first. Move each label together with both quantities. The lookup-based output should retain the expected values, whereas position-based addition would now pair different assets.

A missing label returns #N/A because the formula omits the optional missing-match replacement. A repeated label returns the first match rather than combining its records. These are documented lookup boundaries. Microsoft’s XLOOKUP documentation.

The output includes only the assets listed in J2:J4. Before using your own data, ensure that this requested list includes every asset you intend to report from either location.

Combine repeated asset records one measure at a time

If several rows intentionally belong to the same asset, use conditional summation. With the original sample ranges, enter this complete formula in K2:

=SUMIF($A$2:$A$4,$J2,B$2:B$4)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Copy it right into L2, then copy K2:L2 down through row 4. Moving right changes the quantity ranges to the swatch columns while keeping the label ranges fixed. Each output cell sums only its own asset and measure. Microsoft documents using one range for criteria and another for corresponding quantities. Microsoft’s SUMIF documentation.

To demonstrate repeated records, append Tile-A, 2, and 1 in A5:C5. Before recalculating, extend the first source’s criteria and quantity ranges through row 5:

=SUMIF($A$2:$A$5,$J2,B$2:B$5)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Copy the revised formula across and down as before. The illustrative Tile-A output becomes 14 print copies and 6 swatch cards. A fixed range ending at row 4 would omit the appended record, so range maintenance is part of this method.

Keep each criteria range the same size and shape as its quantity range. Also note that SUMIF treats * and ? as wildcards; the synthetic labels here contain neither. Microsoft’s SUMIF documentation.

Resolve spill errors and check the finished inventory

A spilled formula needs room for its entire output. Place it outside an Excel table, and edit or replace it through its top-left cell. Microsoft documents both restrictions. Dynamic array formulas and spilled array behavior.

If #SPILL! appears, check the intended destination for existing entries and merged cells. Move obstructing content to another location or choose an empty output area. Microsoft identifies blocked destinations and merged cells as causes of spill errors. How to correct a #SPILL! error.

Check quantities for text entries if addition returns #VALUE!. Microsoft notes that direct addition can fail on nonnumeric text, while SUM ignores text. Replacing the formula with SUM would therefore change both the result shape and how text is handled. Microsoft’s SUM documentation.

For an optional labeled result block in Microsoft 365 for Mac or Excel 2024 for Mac, use the original aligned sample on a fresh empty area:

=HSTACK(A2:A4,B2:C4+F2:G4)

This places asset labels beside the calculated measures. HSTACK appends columns; the + inside the formula performs the addition. It does not match labels or correct differing source row orders. Microsoft’s HSTACK documentation.

Finish by checking three things: the output includes every intended asset, each measure remains in its own column, and one manually checked row agrees with the source quantities. For aligned inputs, use range addition. For unique labels in changing row orders, add lookup results. For repeated records, sum each asset’s measures separately. The finished inventory should preserve the detail needed to identify what each quantity counts.

Sources