countifs using 2 "dependent" columns from 2nd sheet

I need to count rows in a 2nd sheet based on a value in current row in 1st sheet.

The 2nd sheet has 2 columns. 1st column is dependent on value from 1st sheet. 2nd column is a dependency based on the value in same row as 1st column.

1st sheet:

Column: "Total_Tickets". Uses a countifs() based on the name found in the same row in the column "Assignee"

The countifs references 2 columns in the 2nd sheet: Assigned_To and Status

The 2nd column, Status tests the value in the same row as found from the first column Assigned_To.

Independently, I can use:

=countifs( {Assigned_To}, $[Assignee]@row ) this returns 3 rows.

=countifs( {Status, AND(@cell <> "Closed", @cell <> "Done" ) this returns 8

My question is: how do I combine these two formulas where the Status values only look at rows matched by the first formula.

In other words, the 2nd formula should return no more than 3 rows because the 1st formula returns only 3 rows.

Data:

Sheet 1:

Assignee Total_tickets

Fred 3 (formula to get the value - =countifs( {Assigned_To}, $[Assignee]@row, {Status, AND(@cell <> "Closed", @cell <> "Done" )

Wilma 1 (same formula as above)

Barney 0 (same formula as above)

Betty 2 (same formula as above)

Pebbles 0 (same formula as above)

Bambam 2 (same formula as above)

Note: The formula is not working. This is what I need to figure out.

Sheet 2:

Assigned_To Status

Fred Closed

Wilma New

Fred Processing

Barney Closed

Betty Processing

Fred New

Fred Done

Bambam Hold

Bambam New

Fred New

Betty New

Again, in the formula, the 2nd criteria should only be looking at rows found from the 1st criteria.

Thanks.

Find more posts tagged with

Accepted answers

Paul Newcome

If you right click on any cell in the working sheet, there should be an option to Manage References. This will give you a comprehensive list of all cross sheet references and cell links.

When you get the notification that the reference already exists, that means you already have a cross sheet reference by that name on the working sheet. If you go into the Manage References tool as mentioned above, you should be able to edit it there.

All comments

Paul Newcome

Are you able to provide a screenshot (with mock data if needed) for reference?

mcav

Sure thing Paul. let me quickly create that.

mcav

TY Paul for asking me to provide a screenshot. To do this, I quickly created 2 new sheets w/ the information above. And the formula WORKED as expected.

Working formula is: =COUNTIFS({Components:Assigned To_Column}, $[Project Resources]@row, {Components:Status_Column}, AND(@cell <> "Closed", @cell <> "In Reporter Review"))

In my case the name before the : represents the Sheet name and the name after the column represents the Column name, with _Column added to the reference name. In other words:

Sheet name: Components

Column name: Assigned To

Column name: Status

After digging around in my real files, I discovered, the reference name was pointing to an older version of the file and not the active version. This was due to creating a copy of the file. When I tried to use the reference, it told me it was already on the sheet yet I did not see/notice it ultimately was referencing an older version.

I deleted the older version, then when I edited it formula, I received a message stating the reference was no longer available. So I was able to created it pointing to the new sheet.

I think 2 things would have helped me here.

1) If we had a place we can view and/or edit references, I would have better seen the reference to older sheets.

2) If when I tried to update the reference to a difference sheet, allow the reference to update (pointing to the new sheet) vs. telling me the reference was already on the sheet. Perhaps showing me the current reference and asking if I want to update it.

I suspect neither of these exist today. Any knowledge if there are feature requests submitted for these already? If there a place I can look to see? And if not, how would I submit a request.

Thanks,

Mike.

Paul Newcome

If you right click on any cell in the working sheet, there should be an option to Manage References. This will give you a comprehensive list of all cross sheet references and cell links.

mcav

Thanks Paul. I have come across that feature yet. When I first started using Smartsheets, I inquired about this internally to my group and no one knew of the feature. Thanks again. It is very helpful.

Paul Newcome

Happy to help. 👍️

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.