Countifs with date range and criteria

Kathleen Howarth

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.

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.

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