adding instances of dates repeating in column A across 11 tabs (sheets) in a sheet

Question

How would I create a formula that shows a sum tally of how many instances each date appears on column A across multiple sheets? I want to make a formula that will display a total tally of how many orders I had each day. All of my orders are spread out out over multiple sheets. The 1st eleven sheets in my master sales sheet contain the entries for all my sale orders. Every sale is given a new row and on those 11 sheets the date of every order is entered in to Column A of those rows. The same date will appear multiple times since I have multiple sales each day. There are dates entered in other places on those sheets but they aren't referencing dates of sales they are referencing other things that aren't related. I only want to tally up the amount of time each date appears in column A across those 11 sheets and nowhere else.

Answer 1

The solution that follows is based fundamentally on the COUNTIF function to get a conditional count, and FLATTEN to combine the dates from multiple columns into a single column. BYROW and LAMBDA are leveraged to create a single formula that can be applied across an entire range. Those 4 functions are listed immediately below however an effort was made in notes to describe the purpose of all functions used, and to link their names to Google support articles that can provide more detail.

Your question describes 11 columns spread across the same number of sheets. This solution includes 3 comma-separated ranges (not 11). It is a simple matter to add the additional ranges to the formulas below using the same syntax.

COUNTIF

The COUNTIF function returns a conditional count across a range.
Syntax: =COUNTIF(range, criterion)

FLATTEN

The FLATTEN function combines values from one or more ranges into a single column.
Syntax: =FLATTEN(range1, [range2, …])

BYROW

The BYROW function groups an array by rows by application of a LAMBDA function to each row
Syntax: =BYROW(array_or_range, LAMBDA)

LAMBDA

The LAMBDA function contains a set of names and a formula_expression that uses them. To calculate the formula_expression, you can call the returned function with as many values as the name declares.
Syntax: LAMBDA(name, formula_expression)

NAMED RANGES

I find using NAMED RANGES instead of <SheetName>!<cell>:<cell> addressing makes formulas easier to follow visually and often easier to maintain. You can do the same, using a similar or different naming convention, or replace the NAMED RANGES with actual range references, as you prefer. Below are the names used for the 3 date columns used in the formulas.

Name	Range
_s1A	Sheet1!A:A
_s2A	Sheet2!A:A
_s3A	Sheet3!A:A

Formula 1

Get count beside existing dates

This formula addresses the use case where there is a column of dates in a sheet (like you showed in your image) and you want to count how many times each of those dates are repeated in a FLATTENED list of all the date columns throughout the spreadsheet.

• BYROW is used to apply the LAMBDA formula to all rows in A:A
• LAMBDA function is used to apply the same formulas to each row. the arbitrary variable r is used to represent the current row being processed.
• IF combined with COUNTA is used to test for, and skip empty cells
• IF combined with DATEVALUE is used to skip non-dates that may be in the list, and since a DATEVALUE will error out on non-dates, it is wrapped in IFERROR to return a NULL value (blank) for non-dates
• COUNTIF is now used to count the number of occurrences of r in our list of dates

=BYROW(A:A, LAMBDA(r, 
   IF(COUNTA(r), 
     IFERROR(
        IF(DATEVALUE(r),
           COUNTIF(FLATTEN(_s1A,_s2A,_s3A),r),
           FALSE)),
     IFERROR(1/0))
))

Formula 2

Get the list of dates

If, unlike Formula 1, the count will not be hosted beside an existing list of dates, a list of dates must then be generated. This list is built by flattening all the date columns throughout the spreadsheet, then filtering out any duplicates, as well as non-date values.

• LAMBDA function is used so that once the list of dates is flattened and sorted, it can be reused based on the arbitrary variable d rather than having to write it out again (in the FILTER function for example)
• FLATTEN function combines all the date columns into a single column and SORT is used (optional) to return the dates in ascending order
• FILTER is used in combination with IF and DATEVALUE to remove non-dates, and since a DATEVALUE will error out on non-dates, it is wrapped in IFERROR to return a NULL value (blank) for non-dates
• Wrapping the formula in UNIQUE allows us to remove duplicates as we only want to check the count for each date once

=UNIQUE((LAMBDA(d, 
   FILTER(d, IFERROR(IF(DATEVALUE(d),TRUE),FALSE)))
   (SORT(FLATTEN(_s1A,_s2A,_s3A)))
))

Combined Formula

Formula 2 as input array for Formula 1

While you can theoretically keep Formula 1 separate from Formula 2, the two formulas can be combined. Formula 2, whose result is an array, becomes a direct replacement for the range A:A in Formula 1's BYROW function.

=BYROW(
  ( UNIQUE((LAMBDA(d, 
     FILTER(d, IFERROR(IF(DATEVALUE(d),TRUE),FALSE)))
     (SORT(FLATTEN(_s1A,_s2A,_s3A)))))
   ), LAMBDA(r, 
     { r, COUNTIF(FLATTEN(_s1A, _s2A, _s3A), r) })
)

---

 

Images

Formula 1: Get count beside existing dates
non-dates (highlighted in red) are ignored: text, numbers, empty cells

 

Formula 2: Get the list of dates

 

Combined: Formula 2 as input array for Formula 1