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.

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.