Countif with Dates

HI all,

I'd like to ask your help. I am trying to count the number of items which are planned for particular dates, so the formula should be easy yet.. I get "0" which is incorrect.

I need this for my metric to later use it for burn up chart.

=COUNTIFS({W2 System Test Tracker Range 1}, [Column2]$2)

the bit "W2 System Test Tracker Range 1" references to other sheet looking at the column with dates provided by the user, and "[Column2]$2" it's referencing a cell with date I am looking for.

Please see the screenshot:

hope this makes sense.

Tags:

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @Helena P.,

    Can you confirm that both the reference cell and the cell range are Date columns/the same column type? Column types have to match to ensure that the COUNTIF statement can compare the data correctly.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Helena P.
    Helena P. ✭✭✭✭

    HI @bisaacs

    thank you for prompt reply! I did wonder about it and the answer is no.

    The metric sheet columns are setup as text/number. I will change this and see if helped :)

  • Helena P.
    Helena P. ✭✭✭✭

    ok, so did this quick and I got error "Date Expected".

  • bisaacs
    bisaacs ✭✭✭✭✭
    edited 05/09/24

    Hey @Helena P.,

    So you're getting a "Date Expected" error (I'm assuming), because you have numbers below the dates (vs the whole column entry data being dates). What I might do if you don't want to completely reorganize the sheet is change the Column 2 type to text/number, that way both the System Tracker range of columns and the reference column are the same data type. See if that works!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Helena P.
    Helena P. ✭✭✭✭

    Hi @bisaacs

    that was my worry 🤣 and I think I am better of with rearranging the sheet as we do need to "enforce" the users to populate the dates only in the sheet I am referring too. i'll work on this and get back to you :)

    thank you!

  • Helena P.
    Helena P. ✭✭✭✭

    ok, I changed the column type to date and still have an error.. is it possible I get an error because the date I am looking at is calculated with a formula not typed in?

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @Helena P.,

    Just to confirm, both the range of columns and Column 2 are Date columns? What error are you getting?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Helena P.
    Helena P. ✭✭✭✭
    edited 05/10/24

    that is correct, both are now setup as date columns and for those where there are items meeting the condition I get error #date expected, for those where there is no items meeting the condition I get blank instead "0"…

  • Helena P.
    Helena P. ✭✭✭✭

    HI @bisaacs

    hope you are well. I managed to solve the issue by flipping the table, however encountered another challenge as i need to count the amount of items meetings more then 2 condition now, where first is the particular date and second condition is the particular status.

    Please see the formula:

    =COUNTIF({W2 System Test Tracker Range 2}, $[Column2]@row, {W2 System Test Tracker Range 3}, "05 Scenario Approved")

    would you be able to help? I wonder if this issue is as I try to count item looking ad the date and something else in different info type.

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @Helena P.,

    If you want to count something using multiple criteria, then you'll want to use COUNTIFS instead of the singular COUNTIF. COUNTIFS only counts a cell if something meets all criteria provided in the formula.

    If you want COUNTIF to count cell if they met either/or certain criteria, when you enter the criteria, you'll put an OR statement there and use "@cell" in the OR statement, like this:

    =COUNTIF(Test:Test, OR(@cell = 123, @cell = 576))

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Helena P.
    Helena P. ✭✭✭✭

    HI , my bad :S silly mistake! I didn't notice I was missing "S" :D all is working now!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!