SOLVED

Countif formula using date from specified cell - but partial match only

Copper Contributor

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")

4 Replies

@Karenaki 

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)

Thanks for the reply. Sorry, but that hasn't counted any results, and there should be one.

In case you need more info, as mentioned, in column I on the Data tab the cell includes the time too (ie 2022-03-03T15:26:24.000Z) so I've made column Z display the date in that format (yyyy-mm-dd) so I can use that to identify cells in column I that match the date. But they won't match entirely.
I've got to count several months of booking dates, so was hoping for a formula, but I can just edit the date in each line... Here I'd put in an asterisk so that it would count the date even though in Col I it's followed by other characters.
ie =COUNTIF('2022 Data'!I1:I5000,"2022-03-04*")
best response confirmed by Peter Bartholomew (Silver Contributor)
Solution

@Karenaki 

I 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")&"*")

 

 

Ah I see. Thanks! It works!! :)
1 best response

Accepted Solutions
best response confirmed by Peter Bartholomew (Silver Contributor)
Solution

@Karenaki 

I 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")&"*")

 

 

View solution in original post