countif current month

Karrie Davis

I was counting for current month. However, I keep getting invalid data type.

 

=COUNTIF({Month}, MONTH(@cell) = MONTH(TODAY()))

Debbie Sawyer

Good Morning

Try this: =COUNTIFS({Month}, IF(ISDATE(@cell), MONTH(@cell)) = MONTH(TODAY()))

It just checks that there is a date in the cell before seeing what the Month of the date is.  If there are any blanks in your date range {Month} then this will break the formula (without the Isdate check in it).

Hope this helps.

Kind regards

Debbie

Ezra

Elegant and robust. well done, Debbie

Paul Newcome

I am not sure if all of the bugs have been worked out or not, but even using Debbie's solution I have run into issues (infrequent and with no clear pattern), so I have worked out a few other choices if you should happen to run into any issues.

 

Debbie's solution SHOULD work perfectly. SHOULD... I went back and forth with Support for quite some time because it doesn't always. Instead of fixing the bug, they said that I should incorporate the DATEONLY function.

 

The DATEONLY function is supposed to have been designed to extract the date from a date/time system column. 

 

Using the ISDATE function should work.

 

But I digress...

 

I never did get any kind of confirmation that there was a bug (even with multiple forms of proof) let alone that the bug was fixed, so here is Support's solution.

 

=COUNTIFS({Month}, DATEONLY(MONTH(@cell)) = MONTH(TODAY()))

.

A variation of Debbie's solution (prone to same bug):

 

=COUNTIFS({Month}, AND(ISDATE(@cell), MONTH(@cell) = MONTH(TODAY())))

.

My initial formula that first triggered the bug (randomly):

=COUNTIFS({Month}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

.

Debbie,

 

Please do not take this as me saying your solution is incorrect or won't work. I have just run into random issues in the past with this. Here's some details on it...

 

[Column1]1: My third solution but with the TODAY function replaced with an actual month number. Returned a proper count.

 

[Column2]1: Same solution as above but with the TODAY function replaced with an actual month number. A different month number, but the same exact range. Returned an error.

.

The range was a single date type column that had a few blanks and a couple of cells containing text. Thus the initial solution using the IFERROR. That SHOULD have worked, but it didn't work consistently. Sometimes it would. Sometimes it wouldn't.

.

Because the IFERROR was randomly throwing an error, I switched to my second solution of using the ISDATE function. Ended up getting the same results as the IFERROR. Sometimes it would work. Sometimes it wouldn't.

.

I never thought to use the DATEONLY function simply because it is used to extract a date from a date/time system column (created or modified). I had no data similar to that anywhere within the range.

 

Most cells were dates, a few were blank, and a few were "TBD".

.

My solutions were working MOST of the time, but every now and then they wouldn't. I eventually discovered another solution that involved switching around the order of my range/criteria sets (I was counting based off of a lot of different criteria). 

 

You would think that as long as it goes range, criteria, range criteria, etc..., it shouldn't matter, but I found I would get the error if the date range was first.

 

Doesn't make sense, but ok. I figured out that I needed to put my date range and criteria anywhere other than first. Annoying, but it worked.

 

Until it didn't.

.

That's when I finally reached out to Support.

 

They kept blaming it on the cells with text in them. Ok. Sure. But why would it work some times and not work other times when it is the same exact range being referenced? They couldn't tell me. All they could tell me was to use the DATEONLY function.

.

So as much sense as it does NOT make... Using the DATEONLY solution is the only way I have been able to get it to work every single time without fail (so far).

Debbie Sawyer

Goodness me!  What a journey!  Thank you for sharing. 

That is what I love about this forum, we can all help each other and learn from each other too.  There is always so much more to pick up and I don't mind learning from anyone!

Thank you Paul.

Kind regards

Debbie

Paul Newcome

Sure thing. It was MONTHS of frustration trying to get it all figured out. I figured it couldn't hurt to put it out there if it will help save someone else the time and frustration.