Countifs Question

I have a sheet that has 2 date columns, 1 is the date we scheduled an activity, the 2nd column is the date that it was actually completed. I need to count the number of times these dates are the same broken out by month.

COUNTIFS([Date1]:[Date1], [Date2]:[Date2], [Date1]:[Date1], >=DATE(2023, 1, 1), [Date1]:[Date1], <=DATE(2023, 1, 31))

I need to write a report that shows every month with this number:

Month comp. on original date comp. on different date

Jan 100 0

Feb 50 23

Mar

Apr

etc.

I'm using that formula above, but it's not working

Find more posts tagged with

Accepted answers

Paul Newcome

You'll need a helper column on the source sheet to indicate whether the two dates are the same or not on each row.

=IF([Date1]@row = [Date2]@row, "Yes", "No")

Then you would count from one of the date columns and this yes/no column.

=COUNTIFS({Date 1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024), {Helper Column}, @cell = "Yes")

All comments

Paul Newcome

You'll need a helper column on the source sheet to indicate whether the two dates are the same or not on each row.

=IF([Date1]@row = [Date2]@row, "Yes", "No")

Then you would count from one of the date columns and this yes/no column.

=COUNTIFS({Date 1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024), {Helper Column}, @cell = "Yes")

marf14

Thank you Paul! It works beautifully. I also had to explicitly say which was were completed on a different date, so I just changed the "Yes" to a "No" a voila….

However, now I am being asked to mark the ones that were completed on a different month, so the month of the original date is March, but it was completed not only on a different date, but a different month, say July. I need to know that

Paul Newcome

Take that same IF and wrap each date reference in a MONTH function

=IF(MONTH[Date1]@row) = MONTH([Date2]@row), "No", "Yes")

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.