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.

Best Answer

Answers

  • Laura B.
    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))

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


  • Laura B.
    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
    Kathleen Howarth ✭✭✭
    Answer ✓

    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.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!