Use FILTER to return every quantity associated with an art-asset label in Excel for Mac. The resulting array can spill into worksheet cells, feed another calculation, or be summed without losing repeated matching entries.

This walkthrough uses synthetic graphic-asset labels and quantities. The task is to select one label, retrieve all its quantities, and reuse those individual numbers in a calculation. Microsoft documents FILTER as returning an array based on specified criteria. FILTER function.

Which Excel for Mac versions support this method?

Microsoft’s current function documentation explicitly lists Excel for Microsoft 365 for Mac, Excel 2024 for Mac, and Excel 2021 for Mac as supporting FILTER. Use one of those editions for the formulas below. FILTER function.

To identify your installation, open Excel and choose Excel → About Excel. Microsoft documents this Mac menu path, and its version-identification guide explains that the Mac application’s About dialog shows the version number and license type. Check the edition as well as the build number. Sharing documents with other versions of Office for Mac; About Office: What version of Office am I using?.

Excel 2019 for Mac and Excel 2016 for Mac are outside the documented availability of FILTER. If you use either edition, the supported path for this particular dynamic-array workflow is to open the workbook in one of the listed Mac editions. The instructions below depend on that prerequisite; they are not a legacy array-formula workaround.

Set up a small asset-quantity example

On a blank worksheet, enter the following data in A1:B7. The first row contains headers. Enter the quantities as numbers, including the zero in B6.

Worksheet row Column A: Asset Column B: Quantity
1 Asset Quantity
2 Tile A 4
3 Arc B 7
4 Tile A 2
5 Dot C 5
6 Tile A 0
7 Tile A 4

Enter Selected asset in D1 and Tile A in D2. Enter Matching quantities in F1. Leave the cells beneath F2 empty so the result has room to expand.

This sample deliberately includes two quantities of four and one quantity of zero for the same label. A correct result must preserve all four matching rows: 4, 2, 0, and 4. Removing repeated values or excluding zero would change the requested result.

Return all matching quantities with FILTER

Select F2, enter the following formula, and press Return:

=FILTER(B2:B7,A2:A7=D2)

The formula uses B2:B7 as the quantity array and A2:A7=D2 as the inclusion test. These ranges cover the same six source rows, satisfying Microsoft’s requirement that the inclusion array have a compatible height or width. FILTER function.

With the sample data, the result occupies F2:F5:

Cell Result
F2 4
F3 2
F4 0
F5 4

Enter the formula only in F2; Excel places the additional results in neighboring cells automatically. Microsoft calls this behavior spilling. To change the formula later, edit the top-left cell of the result, which is F2 here. Dynamic array formulas and spilled array behavior.

The two occurrences of four represent separate matching source rows. Keep them both when your next calculation must account for every occurrence of the selected asset.

Why XLOOKUP does not retrieve these four rows

Microsoft describes XLOOKUP as returning the item corresponding to the first match it finds. It can return multiple columns from a matching row, but that capability does not enumerate every row containing the same label. XLOOKUP function.

For example, the following formula returns only 4 from the sample:

=XLOOKUP(D2,A2:A7,B2:B7)

That result is appropriate when one matching row supplies the answer. For this task, four separate matching rows supply quantities, so use the FILTER result.

Reuse the array in further calculations

Reference the complete result with F2#. Microsoft documents the hash sign as the spilled range operator: it refers to the entire array produced from the starting cell. This avoids fixing a downstream formula to the current four-cell result. Guidelines and examples of array formulas.

Keep downstream formulas beside the result rather than immediately below it, leaving space for more matching quantities. The following examples use columns H through J.

Add all matching quantities

Enter Total quantity in H1, then enter this formula in H2:

=SUM(F2#)

The illustrative result is 10: four plus two plus zero plus four. SUM accepts ranges and adds their numeric values. SUM function.

If you only need the total, you can instead calculate it directly in another empty cell:

=SUM(FILTER(B2:B7,A2:A7=D2))

Choose the visible array when you want to inspect or reuse the individual quantities. Choose the nested formula when the worksheet needs only the final total.

Multiply each quantity by a shared factor

Suppose the illustration requires three copies of each matching quantity. Enter Copies in I1 and the number 3 in I2. Enter Adjusted quantities in J1, then enter this formula in J2:

=F2#*$I$2

The expected results in J2:J5 are 12, 6, 0, and 12. Microsoft documents array calculations that multiply values and return multiple results, and its Mac calculator guide identifies the asterisk as the multiplication operator. Guidelines and examples of array formulas; Use Excel as your calculator in Excel for Mac.

For a single adjusted total, enter the following in H4:

=SUM(F2#*$I$2)

The expected total is 30. These are illustrative calculations using the supplied synthetic data. The arithmetic was checked independently; the workbook formulas were inspected against Microsoft’s documentation rather than executed in Excel for Mac.

Decide what should happen when nothing matches

Change D2 to Wave D, a label absent from the sample. The original two-argument FILTER formula returns #CALC! because Excel cannot return an empty array. Microsoft documents the optional third argument as the way to supply a no-result value. How to correct a #CALC! error.

Choose that value according to what the next calculation needs. The following decision aid applies to this worksheet:

Requirement Formula or approach Consequence
Missing matches should remain visible during calculations Keep =FILTER(B2:B7,A2:A7=D2) No match produces #CALC!, prompting investigation.
The result is primarily a readable list Use =FILTER(B2:B7,A2:A7=D2,"No matches") No match returns a text message.
An absent label should contribute zero to a total Use =SUM(FILTER(B2:B7,A2:A7=D2,0)) No match produces a total of zero.

A fallback of zero is a deliberate calculation rule. It does not mean that a matching source row exists with quantity zero. In this sample, Tile A has a real zero quantity; Wave D has no source rows. Preserve that distinction whenever the presence of an asset matters.

A text message also needs care: Microsoft documents that SUM ignores text in referenced cells. Consequently, summing a result containing only No matches can produce zero and conceal the absence of matching rows. Use the visible-error approach when downstream calculations should stop for a missing label. SUM function.

Restore D2 to Tile A before continuing with the original numeric examples.

Add a second condition only when the task requires it

If a later task specifically asks for positive quantities, use both the label test and a positive-quantity test:

=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))

For the sample, this returns 4, 2, and 4. Microsoft documents multiplication between conditions as an AND test in FILTER. This variation intentionally excludes the matching zero, so retain the original formula when you need every matching quantity. FILTER function.

Troubleshoot the result and keep new rows included

If the formula does not produce the expected four quantities, check the worksheet in this order:

  1. Inspect the output space. A blocked result range causes #SPILL!. Move needed content out of the intended spill area before retrying.
  2. Check the formula’s location. Spilled array formulas belong in the worksheet grid outside an Excel table.
  3. Check workbook dependencies. Microsoft supports linked dynamic arrays between workbooks only while both workbooks are open; refreshing after closing the source can produce #REF!.

These boundaries are documented in Dynamic array formulas and spilled array behavior.

Next, inspect the source labels and quantities. Compare D2 with the intended asset cells, and check that quantities were entered numerically. A plausible total alone is insufficient verification because SUM can ignore text values. SUM function.

The sample formulas reference source rows 2 through 7. If you append data in row 8, edit both source ranges to include it. For example, after entering Tile A in A8 and numeric quantity 3 in B8, replace the formula in F2 with:

=FILTER(B2:B8,A2:A8=D2)

The expected array becomes 4, 2, 0, 4, 3, and the total becomes 13. The F2# reference follows the expanded result, but it cannot make the original source ranges include an omitted row.

For a frequently growing list, Microsoft recommends an Excel table with structured references, which adjust as table rows are added or removed. Keep the spilling output outside that table. Dynamic array formulas and spilled array behavior.

Finish by checking the individual results before relying on the total: for the original six source rows, selecting Tile A should return four quantities, including both fours and the zero. Once that matches, reuse F2# for calculations and choose an explicit no-match rule that fits the worksheet’s purpose.

Sources