Counting number of occurances that meet 2 criteria on another sheet.

Im new to smartsheet and struggling with a formula to return a count of open projects that meet 2 criteria on another sheet .. I want it to lookup number of rows that have the name Erica and In progress in another column on another sheet.

This is a "manual pivot table to use for reporting total tasks completed by each member of my team.

The entire data set is on teh Supplies Team Project and Task sheet. I created a new / blank sheet to show the calculations.

=COUNTIFS({SUPPLIES TEAM PROJECTS & TASKS Range 1,'Erica'}, AND({SUPPLIES TEAM PROJECTS & TASKS Range 2,'In Progress'}))

Any help would be appreciated.

Find more posts tagged with

Accepted answers

Mark Schlerf

Several options, not sure which would work best without understanding more of your situation.

(1) if going with a formula, couple of minor changes... =COUNTIFS({SUPPLIES TEAM PROJECTS & TASKS Range 1},"Erica",{SUPPLIES TEAM PROJECTS & TASKS Range 2},"In Progress") should do the trick. Of course, need to ensure that the ranges are accurately identified. Tip--if you select whole columns when using the "Reference Another Sheet" functionality, this will likely help improve the formula's resilience over time as new projects are added.

(2) if new to smartsheet, this would be a good application for a Report. Create a report like you're creating a new grid, then pull in data from the relevant columsn of the Supplies Team Projects & Tasks grid. You'll be able to summarize the count you're looking for in the function above, while also being able to visualize and manipulate the rest of the data set. It's not a pivot table, although Smartsheet does have a separate offering for those who need to pivot within Smartsheet.

(3) also possible to use the sheet summary tab on the right hand side of the page. More here: Sheet Summary | Smartsheet Learning Center

Hope this helps.

All comments

Mark Schlerf

Several options, not sure which would work best without understanding more of your situation.

(3) also possible to use the sheet summary tab on the right hand side of the page. More here: Sheet Summary | Smartsheet Learning Center

Hope this helps.

B504

Much appreciated.. Worked like a charm..

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.