CountIFS for smartsheet (Excel formula translation to smartsheet)

Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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")

• Options

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.

• Options

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.