Fairly new to Smartsheets - Having trouble with a countifs / date formula. Need your help!

cable_guist

I have a "master data" sheet that has a "DATE OF INCIDENT" field column, and a "FOUND ON A SCHEDULED INSPECTION" field column that populated as either yes or no. These are the two references I'll want to use in my formula on my metrics sheet.

In my metrics sheet I want to use a COUNTIFS statement to tell me how many passed each month for example.

=COUNTIFS({FOUND ON A SCHEDULED INSPECTION}, "NO", [{MONTH({DATE OF INCIDENT})}, "7"])

this WAS THE FORMULA i CAN UP WITH BUT IT RETURNED AN ERROR AS "UNPARSEABLE". Is this because I'm trying to use multiple functions nested together in same cell? I have seen some videos on youtube where users seems to be adding more columns to their "master data" sheet breaking the date column into a column with a numerical month number and column for year. Can anyone explain this? And when to use a report rather than your "master data" sheet for extra coulumns?

thanks in advance for any insight :)

ericncarr

Hello @cable_guist and welcome!

When you're doing a criterion on a range you need to do it after defining the range.

COUNTIFS(range1, criterion1, range2, criterion2…)

In this case that means you need to adjust your formula to this:

=COUNTIFS({FOUND ON A SCHEDULED INSPECTION}, "NO", {DATE OF INCIDENT}, Month(@cell) = 7)

The Month(@cell) = 7 part is basically saying for any given cell within that range that matches the criteria of equaling 7.

cable_guist

@ericncarr And if you wanted to expand that formula further to only count the cells that also match a specific year ?

ericncarr

Hello @cable_guist and welcome!

When you're doing a criterion on a range you need to do it after defining the range.

COUNTIFS(range1, criterion1, range2, criterion2…)

In this case that means you need to adjust your formula to this:

=COUNTIFS({FOUND ON A SCHEDULED INSPECTION}, "NO", {DATE OF INCIDENT}, Month(@cell) = 7)

The Month(@cell) = 7 part is basically saying for any given cell within that range that matches the criteria of equaling 7.

cable_guist

@ericncarr And if you wanted to expand that formula further to only count the cells that also match a specific year ?

ericncarr

@cable_guist

You would expand your criteria for the Date of Incident range with AND.

AND(logical_expression1, logical_expression2,…)

In your case:

=COUNTIFS({FOUND ON A SCHEDULED INSPECTION}, "NO", {DATE OF INCIDENT}, AND(MONTH(@cell ) = 7, YEAR(@cell) = 2024))

I don't know how you have your metric sheet set up, but I usually have dates in one column of the metric sheet so I can use a column formula instead of having to update the formula in every cell which would look something like this:

=COUNTIFS({FOUND ON A SCHEDULED INSPECTION}, "NO", {DATE OF INCIDENT}, AND(MONTH(@cell ) = Month(Date@row), YEAR(@cell) = YEAR(Date@row)))

My date column in the metric sheet would just have the first day of each month, so 7/1/24, 8/1/24, 9/1/24 and so on.

cable_guist

@ericncarr

If I wanted to build a leaderboard with the name of the person who submitted / Reported the most tickets/incidents (essentially my rows in the master sheet), how would I go about using formulas to achieve this output table on my dashboard?

If I have 200 people working at the company using the system submitting tickets then I have a problem with the current smartsheet knowledge.

The only way I know how to do that now is by creating a 200-row table in my metrics sheet that, where each row, would count for a single unique name from the 200 possible. Basically using SUMIF or COUNTIF formula. What is the recommended way to do this efficiently to avoid this extra 200 rows of work?

ericncarr

@cable_guist if I'm understanding correctly you can either use rank or max.

There are multiple ways you could go about, but I would sum the number submitted/reported as a column formula in the main dataset - so do your countif based on month, year, and submitter (or whatever criteria you want to be counting for).

Then in your metric sheet or wherever you want to list the person(s) who submitted the most, you could pull the submitter with an index(collect()) based, again, on whatever criteria you want for that (month or year or all time) and then pull in the number submitted from the column you created in the data set.

Max would get you the top submitter. If you want a certain number of top submitters you could have a column for rank (1, 2, 3, 4…etc.) that uses the rankeq function to pull in the top submitter based on the rank@row.

So only 1 row or as many rows as you want for the number of top submitters you want to trap.