Use SUMIFS in Numbers for Mac to total values from another sheet within a date range. Reference the source table, test its date column twice, and use the following day as an exclusive upper boundary to include the entire final day.

This walkthrough totals synthetic object counts dated April 10–15, 2026, with the answer on a separate summary sheet in the same spreadsheet. It follows Apple’s current Numbers User Guide for Mac, which identifies Numbers 15.4, and Apple’s Functions Help.

Set up the source data and date boundaries

Prepare two sheets named Activity and Summary. On Activity, use a table named Objects with one header row. Enter Date in cell A1 and Count in B1. The sheet and table names matter because the formulas below use both.

Enter these illustrative values in rows 2–7. Type each date formula into its specified cell, and enter the counts as ordinary numbers. Using DATE(year, month, day) avoids ambiguity between regional date orders. Apple documents that DATE returns a date/time value consistently regardless of the date format in your Language & Region settings. DATE — Apple Functions Help

Row Column A: date formula Column B: count
2 =DATE(2026,4,9) 4
3 =DATE(2026,4,10) 6
4 =DATE(2026,4,12) 8
5 =DATE(2026,4,15) 5
6 =DATE(2026,4,15) 3
7 =DATE(2026,4,16) 9

On Summary, prepare a table named Window with the following cells. Leave B4 empty for the total. The upper boundary is April 16 because the intended reporting period ends after all of April 15.

Cell Label or value to enter
A1 Setting
B1 Value
A2 Start date
B2 =DATE(2026,4,10)
A3 Stop before
B3 =DATE(2026,4,16)
A4 Object total

For readable dates, select the date cells and choose Format sidebar → Cell → Data Format → Date & Time. Select a date display and, if desired, set the time display to None. Hiding the time changes what you see; Numbers still uses the underlying time in calculations. Format dates, currency, and more in Numbers on Mac

Enter the cross-sheet SUMIFS formula

Select B4 in the Window table on Summary, and enter this complete formula. Press Return to commit it. Apple documents typing = to open the formula editor and pressing Return to save a formula. Calculate values using data in table cells in Numbers on Mac

=SUMIFS(Activity::Objects::B2:B7,Activity::Objects::A2:A7,">="&B2,Activity::Objects::A2:A7,"<"&B3)

The expected total is 22 objects: the qualifying counts are 6, 8, 5, and 3. The April 9 count falls before the start; the April 16 count reaches the excluded upper boundary. These are illustrative inputs and an independently checked calculation, rather than a result from running Numbers.

Read the formula in five parts:

Formula argument Purpose
Activity::Objects::B2:B7 Values to add
Activity::Objects::A2:A7 Dates to test against the start
">="&B2 Include dates at or after the start
Activity::Objects::A2:A7 The same dates, tested again
"<"&B3 Include dates before the stop boundary

SUMIFS includes a corresponding value only when every condition is satisfied. All collections must have the same size, so the count range and both date ranges cover the same six rows. SUMIFS — Apple Functions Help

The quoted comparison operator and the cell reference are joined with &. Keep the reference outside the quotation marks: ">="&B2 uses the value in B2; putting the whole expression inside quotes would supply literal text. Apple documents & as the operator for joining strings and cell contents. Add and edit formulas — Apple Functions Help

Build the references by selecting cells

If your source names differ, build the references by selection while editing the formula: switch to the source sheet and select the required range for each argument. Numbers automatically includes the sheet and table names when you select cells on another sheet. Its documented structure is Sheet::Table::Range. Calculate values using data in table cells in Numbers on Mac

For this example, all three source references must point to Activity::Objects. The unqualified B2 and B3 refer to the boundary cells in the summary table containing the formula. This walkthrough uses sheets inside one spreadsheet document; its references contain no external filename.

Choose boundaries that include the intended final day

Apple defines every date/time value as containing both a date and a time, even when only one is displayed. That makes an end date’s hidden time consequential. Types of arguments and values — Apple Functions Help

For a calendar-day task, the practical rule derived from that behavior is to include the start of the first day and exclude the start of the following day. For April 10–15, use April 10 as the start and April 16 as the stop. You do not need to choose a final second on April 15.

Intended task Start boundary Upper boundary Comparisons
Include April 10–15 April 10, midnight April 16, midnight >= start, < stop
Include April 15 only April 15, midnight April 16, midnight >= start, < stop
Include all of April April 1, midnight May 1, midnight >= start, < stop

An inclusive upper test such as "<="&B3 is appropriate when B3 contains the exact last instant you intend to include. If that cell contains April 15 at midnight, later entries on April 15 are beyond the boundary. With the sample data, changing the three-object entry to April 15 at 4:30 p.m. illustrates the difference: the next-day exclusive approach still includes it; an upper boundary of April 15 at midnight excludes it, reducing the total to 19.

To inspect an existing entry, select its date cell and look at Smart Cell View at the bottom of the window. Apple documents that it shows both the actual date and time, including components hidden by the display format. Format dates, currency, and more in Numbers on Mac

Diagnose an error or an unexpected total

Check the setup in this order so that you can isolate the problem before changing the formula’s logic:

  1. Confirm the boundaries. For the sample, B2 must be April 10 and B3 April 16. A stop boundary at or before the start cannot describe the intended interval.
  2. Confirm the source table. Check that every source reference identifies Activity::Objects.
  3. Match the rows. Use B2:B7 with A2:A7, rather than shifted ranges such as A3:A8. Equal size is necessary, and matching row positions ensures each count is paired with its own date.
  4. Check date values. Test a suspect source date with the formula below in an unused cell.
  5. Inspect any error message. Click the error icon in the result cell for details before replacing the expression.
=ISDATE(Activity::Objects::A2)

ISDATE returns TRUE when the expression evaluates to a date and FALSE otherwise. For this sample, the test should return TRUE. If it returns FALSE, correct that entry to a genuine date/time value and repeat the check for other suspect rows. ISDATE — Apple Functions Help

The sample formula explicitly covers rows 2–7. When extending the data, review all three source ranges together. Apple also specifically cautions that, in a table using categories, adding a row within a referenced range does not include the new row in formula results unless you change the reference. Calculate values using data in table cells in Numbers on Mac

Keep the result separate from highlighting and charts

This task needs one total in one cell. Direct table ranges provide the required inputs; no array construction is necessary. Apple’s definition of a collection allows a table-cell range or an array returned by an array function, but SUMIFS still requires equally sized collections. Types of arguments and values, SUMIFS

Conditional highlighting changes a cell’s appearance according to its value. Keep the date tests in the calculation rather than treating a fill color as a selection criterion. Apple’s highlighting controls are under Format → Cell → Conditional Highlighting; if multiple rules match, the first matching rule determines the appearance. Use cell conditions in Numbers on Mac

If you later compare several reporting windows, first calculate each window’s total in table cells and chart those results. Numbers charts use table data and update when that data changes; Apple also permits chart references to tables on different sheets. Select data to make a chart in Numbers on Mac

For your own object log, replace the sample names and ranges, then verify one short interval by adding its qualifying rows manually. Once that total agrees, retain the same boundary convention: the first included day in the start cell and the first excluded day in the stop cell.

Sources