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.

Find more posts tagged with

Accepted answers

Paul Newcome

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.

All comments

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

RCA

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.

RCA

Paul, the is helpful

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.