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!

1
  • share a copy of your sheet
    – user0
    Jul 25, 2019 at 16:20

1 Answer 1

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

1
  • Thanks a million. That works a treat! Jul 25, 2019 at 20:59

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.