SOLVED

COUNTIF to count cells with dates in them

Copper Contributor

It's so simple, but I can't make it work.... I have a column where a date will be entered for some cells. I just want to count the cells with dates in them. The other cells will be blank.

 

I've tried to use the * to count any value in a cell,=COUNTIF(G3:G20,"*") and that didn't work. 

 

Is there some variation that will count date values?  The column in question is formatted as "DATE".

 

Help.

11 Replies
best response confirmed by Cheryl4242 (Copper Contributor)
Solution

@Cheryl4242 

 

Hi Chery,

You can use CountA() function. Please refer attached file.

Thanks,

Tauqeer

CountA1.JPG

 

@tauqeeracma  THANK YOU SO MUCH!  I didn't even know this was an option!  Thank you!

@Cheryl4242 

COUNTA() is the best in this case, but if check non-blanks with COUNTIF/COUNTIFS it looks like

=COUNTIF(G3:G20,"<>")

@Sergei Baklan thank you!! That´s what I was looking for!!!

@SamCano , glad to help

This was really helpful...thank you

@Sergei Baklan 

@tauqeeracma This was a great help to me also.

 

Thank you,

 

Johnn557.

@Cheryl4242 

count dates.png

 

 This solution might come handy. I have written the formulas above. The DATEVALUE returns a number if a valid date is passed to it. However, it requires the date in Text format. Therefore, I had to join it with and emptry string using the & ampersand sign. So, if a valid date will be encountered then you get a number as a result. We can check if the result is a number or not by using ISNUMBER and get an array of TRUE and FALSE. TRUE for all dates and FALSE for any text, simple numbers and wrong dates. Then we can simply use COUNTIF(range, "TRUE") which will give the exact number of cells containing the dates. This will also take care of blank cells in between. We'll always get a FALSE for the blank cells.

I hope that helps and improves the solution.

@tauqeeracma Hello, I wanted to count say how many appointments someone had in a given month so trying =COUNTIF('Appointments’!O4:O2974,"=">"01/07/2023,<31/07/2023") I'm using the format the dates are in that range. It's returning 1 when there are many more there? Any help appreciated thanks. 

Hi @RRoux 

 

You can use COUNTIFS() function to achieve your requirements. Please refer to the attached sample file for more understanding.

tauqeeracma_0-1688480751018.png

 

Hope it will help you.

 

Thanks

Tauqeer

@tauqeeracma 

 

How do you count the cells with just dates on them?

beigemc_0-1701892069199.png

 

1 best response

Accepted Solutions
best response confirmed by Cheryl4242 (Copper Contributor)
Solution

@Cheryl4242 

 

Hi Chery,

You can use CountA() function. Please refer attached file.

Thanks,

Tauqeer

CountA1.JPG

 

View solution in original post