using COUNTIF to find exact numbers.

I've been using COUNTIF to count the number of occurrences in a month.

So i've got a list of 'events' throughout the year, all with different dates.

I'm wanting to count how many are in each month. What I've done is to use the MONTH formula in another column to give a numerical value of the month in 1 - 12.

And then using a formula to count how many 1's, how many 2's etc. This tells me how many occurrences there are in each month.

=COUNTIF({Improve Data Range 2}, FIND("1", @cell) > 0)

However I noticed the total number was getting a bit high, and realised that my formula for finding '1' was also counting the 1 in 10, so was counting all of Octobers occurrences. I guess by this logic it would also count the 1 in 11, and 12.

Is there to make this formula only identify a singular 1, and not pick up a 1 in a bigger number?

Alternatively is there a way to make the MONTH formula spit out a Month name such as 'Jan' 'Feb' rather than a numerical value? That would be a unique value that countif couldn't mix up.

Thanks

Find more posts tagged with

Accepted answers

David Joyeuse

Hi @GrahamR

Why didn't you use this formula in your COUNTIF:

=COUNTIFS({Date Range first cell}, MONTH(@cell)= 1) for January and so on...

That will count dates that suits the MONTH you want to count. You don't really need the helper column here.

Also, there's no way MONTH will return a string. If you want to do that, you're gonna need to use an helper sheet with some INDEX/MATCH to link each number to the desired string.

Hope it helped!

All comments

David Joyeuse

Hi @GrahamR

Why didn't you use this formula in your COUNTIF:

=COUNTIFS({Date Range first cell}, MONTH(@cell)= 1) for January and so on...

That will count dates that suits the MONTH you want to count. You don't really need the helper column here.

Also, there's no way MONTH will return a string. If you want to do that, you're gonna need to use an helper sheet with some INDEX/MATCH to link each number to the desired string.

Hope it helped!

David Tutwiler

Normally, I'm in favor of splitting out functions so they don't get too messy. However, I think you're best bet will be to add the MONTH() function into this function to make sure you get the right month. Something like:

=COUNTIF({Improve Data Range 3}, MONTH(@cell) = 1)

This assumes that Improve Data Range 3 is the column that contains the date.

Graham Rimell

Brilliant thanks. Took me a few tried to get it working, but seems to work a treat.

Graham Rimell

I'm actually having some issues with cross referencing the date column from another sheet. I'm using:

=COUNTIFS({impdate}, <>. MONTH(@cell) = 1)

it keep returning #INVALID DATA TYPE.

David Tutwiler

Have you tried:

=COUNTIFS({impdate}, NOT(MONTH(@cell)=1))

If that doesn't work, you'll want to look at your column type to make sure it's a date format.

Graham Rimell

Thanks,

It is still coming up with the error. It seems the MONTH function doesn't like empty cells when Cross linked to other sheets. So the empty 10 cells that appear at the bottom of the sheet are causing the error.

I need a way for it to ignore cells that don't contain dates. A sort of ISDATE = True function, but I'm not sure how to construct that within the formula.

David Tutwiler

You could do:

=COUNTIFS({impdate}, NOT(MONTH(@cell)=1), {impdate}, NOT(ISBLANK()))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.