Countifs with date range and criteria

Hi, I am trying to do a formula to count the # of rows the have "Waller" as the L3 leader between the dates of Nov 1, 2020 and January, 31, 2021. I am trying to create a these metrics in a separate sheet.

This is what I have but it is not adding.

=COUNTIFS({Sheet - NA Market Travel Approval Range 3}, {Sheet - NA Market Travel Approval Range 3} = "Waller", {Sheet - NA Market Travel Approval Range 2}, >DATE(2020, 11, 1), {Sheet - NA Market Travel Approval Range 2}, <=DATE(2021, 1, 31))

Thanks in advance.

Find more posts tagged with

Accepted answers

Kathleen Howarth

Thanks for the research. I do have Waller matched exactly. The date is a system date which is the date created and the format is the following. Wondering if that is the problem 07/24/21 6:15 AM.

All comments

Laura B.

Should your first DATE section be ">=" instead of just ">"?

=COUNTIFS({Sheet - NA Market Travel Approval Range 3}, {Sheet - NA Market Travel Approval Range 3} = "Waller", {Sheet - NA Market Travel Approval Range 2}, >=DATE(2020, 11, 1), {Sheet - NA Market Travel Approval Range 2}, <=DATE(2021, 1, 31))

Kathleen Howarth

I changed to ">=" and it still had a count of 0.

Laura B.

Hi @Kathleen Howarth ,

I used your formula with my own data (where Range 3 was a column with names and Range 2 was a column with dates) and was able to get the correct counts.

Two things to check:

In the column "Sheet - NA Market Travel Approval Range 3" is "Waller" listed exactly like that, with no extra spaces or other characters?

Is the column "Sheet - NA Market Travel Approval Range 2" formatted with a Column Type of Date?

Kathleen Howarth

Thanks for the research. I do have Waller matched exactly. The date is a system date which is the date created and the format is the following. Wondering if that is the problem 07/24/21 6:15 AM.

Kathleen Howarth

I was only including a portion of the name in the column and that fixed it! thanks so much

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.