1

I would like to know which days of the month customers have historically paid on the most. I would like to create a report that has days 1,2,3,4...31 all with a count next to each day for how many times it occurs in our date column. I would also like to do the same with the days of the week if possible. Any help would be greatly appreciated!

  • share a copy of your sheet – user0 Jul 25 at 16:20
0

=ARRAYFORMULA(QUERY(DAY(INDIRECT("A1:A"&COUNTA(A1:A))), 
 "select Col1,count(Col1) 
  group by Col1 
  label count(Col1)''", 0))

0


=ARRAYFORMULA({ROW(A1:A31), IFERROR(VLOOKUP(ROW(A1:A31), 
 QUERY(DAY(INDIRECT("A1:A"&COUNTA(A1:A))), 
 "select Col1,count(Col1) 
  group by Col1 
  label count(Col1)''", 0), 2, 0), 0)})

0


=ARRAYFORMULA({{"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}, 
 IFERROR(VLOOKUP({"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}, 
 QUERY(TEXT(INDIRECT("A1:A"&COUNTA(A1:A)), "ddd"), 
 "select Col1,count(Col1) 
  group by Col1 
  label count(Col1)''", 0), 2, 0), 0)})

0


spreadsheet demo

  • Thanks a million. That works a treat! – George Young Jul 25 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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