Google sheets Countif function with UTC timestamp

0
votes

I am trying to count number of IDs that exist as of a particular date (see Summary tab). However, since the dates (column A) in raw data tab are in UTC timestamp format, the formula returns 0. My formula is countif('raw data'!A:A,"<="&A2)

  1. How do I adjust the formula to convert UTC timestamps to datevalue within countif function.
  2. I also would like to make this countif function work with arrayformula so that I don't have apply the formula on each row. Here is the sample sheet : https://docs.google.com/spreadsheets/d/1IFAAiguhKD0xK8SYsnzMBSar4IFAQPzZfIh0U44bZWI/edit?usp=sharing
1
google-sheets-formula

1 Answers

0
votes

Translate first your dates by

=arrayformula(if(A2:A="",,VALUE(left(A2:A,10))))

You can use

=sumproduct(('raw data'!D:D<=A2)*('raw data'!D:D<>""))

Now, if you want arrayformula to be applied, first make a query

=QUERY('raw data'!A:D,"select D,count(B) group by D")

then compute a total as following

={"Total ID";ArrayFormula(if(isblank(G2:G),,mmult(1*(transpose(row(G2:G))<=row(G2:G)),1*G2:G)))}

both formulas are on yellow cells