Countifs and Contains (Name & Date)

Hello All!

A bit of a tricky one here - I am trying to COUNT the amount of times a person has an item for each month. The problem is when I use CONTAINS and select the month it shows anywhere that number shows.

For example, I am trying to find each time a person has something with a date of anytime in August (8).. when I use 8 it collects other dates like 7/08/20 but I don't want JULY I only want August.

Any thoughts?!?

=COUNTIFS({Name}, Name@row, {Lease Move in Log Range 1}, FIND("8", @cell) > 0)

Find more posts tagged with

Accepted answers

Leslie Merlino

Use a date formula instead. I don't know what your source sheet looks like, but try:

=COUNTIFS({Name}, Name@row, {Lease Move in Log Range 1}, IFERROR(MONTH(@cell), 0) = 8)

If you have more than one year in your sheet, this will not reference the year. It will count all entries with 8 as the month regardless of year. If you need a year breakdown as well, use this one:

=COUNTIFS({Name}, Name@row, {Lease Move in Log Range 1}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 8))

All comments

Leslie Merlino

Use a date formula instead. I don't know what your source sheet looks like, but try:

=COUNTIFS({Name}, Name@row, {Lease Move in Log Range 1}, IFERROR(MONTH(@cell), 0) = 8)

If you have more than one year in your sheet, this will not reference the year. It will count all entries with 8 as the month regardless of year. If you need a year breakdown as well, use this one:

=COUNTIFS({Name}, Name@row, {Lease Move in Log Range 1}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 8))

Nicole Hodges

IT WORKED!! THANK YOU SO MUCH!

Leslie Merlino

You are welcome!! Glad I could help.

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.