How to use COUNTIF on a Smartsheet Report

becca moon

Hello - I have a report that manages all past due, in progress, and upcoming tasks sorted by team member. I am trying to use the COUNTIF formula on my Metric Sheet to count the number of past due tasks by team member in the report. My goal is to use my Metric Sheet to gather the number of past due tasks by team member and use that information in a chart.

KPH

Hi @becca moon

You cannot use formulas on, or from, a smartsheet report.  You would need to create your formulas based off of the source sheets, rather than the report. Here are some instructions for how to do that, and at the end, an alternative.

If you have the data in a SHEET want to count multiple ifs then you need a COUNTIFS function. The syntax is explained here:

https://help.smartsheet.com/function/countifs

If you data looks like this (I have put in some fake names as you need names in the formula):

To find the number of Red flagged tasks assigned to ES, your COUNTIFS would be:

=COUNTIFS(Status:Status, "Red", [Assigned To]:[Assigned To], "Enid Smith")

You can change the parts in bold to count for other team members or other statuses:

=COUNTIFS(Status:Status, "Red", [Assigned To]:[Assigned To], "Enid Smith")

You could also put those details into your sheet and reference them from the sheet rather than creating a new formula for each person/status. Like this:

If you do this you only need one formula (used in all 6 cells in the table):

=COUNTIFS(Status:Status, $[Column4]@row, [Assigned To]:[Assigned To], [Column5]$9)

If you are using this formula on a separate sheet to the one with the data in (which is best practice) you will need to replace the column names with cross sheet references. You can do this using the Reference Another Sheet link:

If you are new to those - they are explained here: https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

------

If the data is only in a REPORT, and you cannot collect all the data into a sheet to analyze, then you could try grouping the data on the report to show what you need. Group by Assigned To

and then Status and Summarize the count of Status

To create this

No formula needed.

becca moon

@KPH Thank you, this is very helpful!! This data is only available in a report, so I am trying to group the data by "Assigned To" and it is not available as an option. I read some other discussions and I figured it may be because the Assigned To column is multi-select. How would I turn off multi-select? I do not see an option to edit the column properties.

KPH

Hi @becca moon

The group by and summarize options are selected at the top, not by right clicking on the column name:

There is an issue with grouping by assigned to if you have multiple contacts within your assigned to cell. You would need to change the sheets. But I don't think that is the problem.

becca moon

@KPH Hi! Yes, I clicked on Group at the top and "Assigned To" is not an option that populates. I figured I needed to turn off the multi-select on this report but I cannot edit the "assigned to" column properties - do I need to go back to the other reports that this report is pulling from and make sure they are not multi-select?

KPH

Hi @becca moon

The multi-select is specified in the sheets that are being summarized by the report. A quick way to see if this is the issue, is to remove the sheets with multi-select and leave only those with single entries and see what happens.