Fairly new to Smartsheets  Having trouble with a countifs / date formula. Need your help!
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 :)
Best Answers

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.

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

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.

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

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. 
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 200row 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?

@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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!