Mar 07 2022 03:20 AM
I have a set of bookings in one tab (2022 data), with their registration date in column I.
I want to use COUNTIF formula to count how many bookings we've received on each date, on another tab, so that I can see booking trends and make a graph.
However, the registration column has the date AND time, so I only want to use part of that column to identify it as being on the date. So an asterisk needs to go in there somewhere?
I've entered all dates in column Z on the second, current tab, to count next to each one.
So in words I want to ask 'count how many times 'this date' (using col Z) appears in PART OF column I of '2022 Data' tab'.
I'm using this formula (below) but need to add the bit about Z2 matching just 'part of' column I. Where do I put the asterisk please?
=COUNTIF('2022 Data'!I1:I5000,"&Z2")
Mar 07 2022 03:32 AM
Use COUNTIFS to count the number of cells with a value greater than or equal to that of Z2, and less than Z2+1.
=COUNTIFS('2022 Data'!I1:I5000,">="&Z2,'2022 Data'!I1:I5000,"<"&Z2+1)
Mar 07 2022 03:40 AM
Mar 07 2022 03:46 AM
SolutionI had assumed that the cells contained 'real' date/time values. However, Excel does not recognize 2022-03-03T15:26:24.000Z as a date; it treats it as text. Try
=COUNTIF('2022 Data'!I1:I5000,TEXT(Z2,"yyyy-mm-dd")&"*")
Mar 07 2022 03:49 AM
Mar 07 2022 03:46 AM
SolutionI had assumed that the cells contained 'real' date/time values. However, Excel does not recognize 2022-03-03T15:26:24.000Z as a date; it treats it as text. Try
=COUNTIF('2022 Data'!I1:I5000,TEXT(Z2,"yyyy-mm-dd")&"*")