countifs using 2 "dependent" columns from 2nd sheet

Options

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.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!