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. enter image description here

1 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.


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


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


The BYROW function groups an array by rows by application of a LAMBDA function to each row
Syntax: =BYROW(array_or_range, 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)


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

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

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.

   ), LAMBDA(r, 
     { r, COUNTIF(FLATTEN(_s1A, _s2A, _s3A), r) })



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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.