COUNTIF for dates equal to current month or current week

I'm attempting to create a metric that counts the number of installs in the current month (and also another for those in the current week).

I have tried the following:

=COUNTIF({Master Range 1}, =MONTH(TODAY()))

=COUNTIF({Master Range 1}, =WEEKNUMBER(TODAY()))

I've also tried using SUMIF instead of COUNTIF.

All return "0" even though there are dates that match both criteria.

What am I missing?

Find more posts tagged with

Accepted answers

All comments

Nasir@EBC

The following formulae will deliver the desired result:

=COUNTIF({Master Range 1}, MONTH(@cell) = MONTH(TODAY()))

=COUNTIF({Master Range 1}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

Hope it solves your problem.

Best

Nasir (Zealvert)

Sldollman

Thank you @Nasir@EBC I've tried these same formulas before (and now again) and I'm receiving an #Invalid Data Type error. This seems to indicate the column I'm "counting" is not a date, but it is in fact a date field.

I tried changing my lookup range in the reference to rows 1-1000 instead of the full column and I received a #Date Expected error. Seems that something is wrong in my master sheet.

Any ideas how to find the problem? I've verified the master sheet column is a date column. The dates in the field look correct (when I hover over them, they show actual dates). I don't see anything wrong, but I'm not sure how to verify every cell (there are 1,000 rows).

Paul Newcome

Double check that your column is set to a Date type column then try @Nasir@EBC's solutions.

Sldollman

@Paul Newcome I've triple checked it. The Date column on the master list is set as type=Date.

As per my post above:

I've verified the master sheet column is a date column. The dates in the field look correct (when I hover over them, they show actual dates). I don't see anything wrong, but I'm not sure how to verify every cell (there are 1,000 rows).

Paul Newcome

Do you have any errors, non-date type data, or blanks in the range? If so, you will want to either correct those or incorporate an IFERROR statement into your COUNTIFS.

=COUNTIF({Master Range 1}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

Sldollman

@Paul Newcome The IFERROR solved the problem! There must be invalid data somewhere in the column. Thank you so much!

Paul Newcome

@Sldollman Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others encountering the same issue can know that a solution may be found in this thread.

Sldollman

@Paul Newcome ... Well, the formula is now returning a number, but the number is not correct... I'll keep working on it...

Paul Newcome

@Sldollman Double check your source data. Do you have any formulas or anything in the Date column?

Sldollman

@Paul Newcome Source data is date only (no formulas). The master list is populated with data pulled directly from a database. I'll check the data in the database for errors.

Paul Newcome

If you use ctrl+f and search for the # in the date column of your master list, it will jump you to any errors. Depending on how the data is being pulled, there may be data in the column that LOOKS like a date but is actually text. That is only slightly trickier to look for, but it can be found.

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.