Formula question

ashdrap

Hi, I am trying to calculate the amount each member has completed. I tried countifs but it is telling me the wrong formula. I am doing it off this sheet. I had the formula and now just can't get it.

Matt Johnson

Hi @ashdrap

=COUNTIFS([Assigned To]@row, "Ashley Drapkin-Grossi", Status@row, "Complete")

That's one version of the formula. I would list all the employees on a different sheet and the do the formula to point the criteria at the Assigned To name column@row so you could drag it down and only do the formula once.

Or you could keep the formula as suggested and maybe it put it on the Sheet Summary pop out window, changing each one to be for each employee. You could then do a report off all the sheets that you are doing this for to get the full count for earch across the Portfolio, is you're set up that way.

I hope that helps.

Matt

ashdrap

Hi,

What am I doing wrong?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/362379#Comment_362379

You want these to reference the entire Assign To and Status columns from the other sheet.

When entering your formula, when you get to the first parentheses - =COUNTIFS( - Smartsheet will pop up a little helper box. One of the things in the helper box says "Reference another sheet." Click on that, navigate to the sheet with all your data, and select the Assign To column by clicking on the column header. The click OK or whatever. That will create a reference in you formula inside curly braces that looks something like this: =COUNTIFS({Name of the data sheet Range 1}.

Next, use @row to select the name from the left hand column as the criteria to search for with the Assign To column range. If the blue column listing Tabitha, Gus, David, Shelby in the screenshot above is called Name, it would look like this. This says 'count the rows from the data sheet where the name in Assign To equals the name on this row.

=COUNTIFS({Name of the data sheet Range 1}, [Name]@row,

Once you hit the spacebar after the last comma above, you'll get the same little helper box as before. Repeat the process to set the range of the Status column from the data sheet, and set the criteria as "Complete":

=COUNTIFS({Name of the data sheet Range 1}, [Name]@row, {Name of the data sheet Range 2}, "Complete")

Voila! You can then just copy this formula to the other rows and it will work for those other peoples' counts as well.