CountIFS for smartsheet (Excel formula translation to smartsheet)

I have an excel formula that calculates the the number of days by month and status.

=COUNTIFS($E$1:$E$100,">=Aug 01 2021",$E$1:$E$100,"<=Aug 31 2021",$G$1:$G$100,"=Not Started")

When I translate this formula into smartsheet it does not work.

=COUNTIFS([End Date]1:[End Date]100,">=Aug 01 2021",[End Date]1:[End Date]100,"<=Aug 31 2021",Status1:Status100,"=Not Started")

Please not I changed the End Date column to Date which resulted in translation of MM/DD/YYY.

The formula does not return a value in the cell. Is this formula possible in smartsheet? Thank you for any assistance anyone can provide.

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The formula in Smartsheet for August of 2021 would look like this...

    =COUNTIFS($E$1:$E$100, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021), $G$1:$G$100, @cell = "Not Started")


    To write it with specific dates instead of referencing a month and year number it would look more like this...

    =COUNTIFS($E$1:$E$100, AND(@cell >= DATE(2021, 08, 01), @cell <= DATE(2021, 08, 31)), $G$1:$G$100, @cell = "Not Started")

  • Paul, the is helpful

    For clarity the "@cell" in your string example should I just copy the column length again as I need to search the enter column for a specific month? Would it look like =COUNTIFS([End Date]1:[End Date]100, and ([End Date]1:[End Date]100>=DATE(2021,08,01),[End Date]1:[End Date]100>=DATE(2021, 08, 31)),Status 1:Status 100, Status 1:Status 100="Not Started")

    Note: Smartsheet brings up the column name in formula, I need an example of @cell since this is a range.

    Thanks again for your assistance.

  • Paul, the is helpful

    For clarity the "@cell" in your string example should I just copy the column length again as I need to search the enter column for a specific month? Would it look like =COUNTIFS([End Date]1:[End Date]100, and ([End Date]1:[End Date]100>=DATE(2021,08,01),[End Date]1:[End Date]100>=DATE(2021, 08, 31)),Status 1:Status 100, Status 1:Status 100="Not Started")

    Note: Smartsheet brings up the column name in formula, I need an example of @cell since this is a range.

    Thanks again for your assistance.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    No. You would leave the @cell as is. It is basically telling the formula to count the previously established range on a cell by cell basis.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!