Google-sheets – Need to count unique occurrences past 365 days from date of service Google Sheets

formulasgoogle sheets

I'm trying to count how many visits has occurred in the past 365 days in Google Sheets. Each account is in rows and the dates of transactions are dated in the columns. I will be listing individual visits by date.

I'm not sure if it would be a =countuniqueifs or how to really do this.
Any help would be greatly appreciated as I'm stumped.

Best Answer

Assuming that account names are in sheet Visits column A2:A, and dates are in B2:K, try Insert > New sheet and this formula in cell A1 of the new sheet:

=arrayformula( 
  query( 
    flatten( 
      if( 
        Visits!B2:K >= today() - 365, 
        Visits!B2:K, 
        iferror(1/0) 
      ) 
    ), 
    "select Col1, count(Col1) 
     where Col1 is not null 
     group by Col1 
     label Col1 'date' ", 
    0 
  ) 
)

Format column A as Format > Number > Date.

Related Topic