Best Of

If you want to count rows that meet more than one criteria, then you will need to use a COUNTIFS function, not COUNTIF

See:

COUNTIFS Function | Smartsheet Learning Center

In the COUNTIFS you will need two pairs of ranges and criteria. The Date Opened and month will work as one pair, so start with:

=COUNTIFS( [Date Opened]:[Date Opened], IFERROR(MONTH(@cell ), 0) = 1)

That will return the rows with January Date Opened.

Then add in a range and criteria for the Reference #.

[Reference #]:[Reference #]

Will be the range. I've put 001 as the criteria for now.

=COUNTIFS( [Date Opened]:[Date Opened], IFERROR(MONTH(@cell ), 0) = 1,[Reference #]:[Reference #],"001")

This will return a count for rows where the reference # is 001 and the date opened is January

You can replace the 001 with whatever reference you want.

Your first formula (=COUNT([Reference #]:[Reference #])) is counting all rows with data in [Reference #] not a specific reference number.

@AKnight that worked like a charm! I appreciate the detail!! i've been doing alot of dashboards lately to measure productivity and progressions, so this is helpful! im slowly creating my own user guide. I need you on speed dial! haha

THANK YOU AGAIN!!!!!! ❤️

@Cayla Davis Either! Whatever is standard for your organization. I always suggest using the sheet summary where the data came from to make it easier to write the formulas and personally I find it easier especially when the data is being presented elsewhere.

If you do house the formula in a different sheet you will have to set up a cross-sheet reference and you can use the formula =COUNT({Numbering Column from Canada Sheet}).

Some additional information to help on your smartsheet journey:

• The {} type brackets indicate that it is a reference from a different sheet, the [] brackets are calling to columns that exist in that sheet.
• Something to consider when developing this is how the user will be interacting with the data. If it is just a dashboard and they don't look at the source sheet, then you can probably store the metrics in that summary section on that sheet to reduce the number of sheets you need to create.
• Another thing to consider is what widget is being used, charts will require a sheet or report creation while metrics just need a cell to call to meaning you can put it in on the sheet itself.
• I really only do sheet creation as a last resort because of the time it takes to create and to avoid over-cluttering folders. First I see if it can be a helper column that can be hidden, if its a metric I put it in the sheet summary, if the data already exists and I am not making a fancy chart, I make reports.

Let me know if any of that doesn't make sense. The additional info has a lot of personal preferences and suggestions I make to companies but someone else might differently do it. Remember there are usually always multiple ways to get a correct answer.

I figured out how to add the dollar symbol $ to a JOIN formula and wanted to share.

My original formula joined all of the cost/unit for the children below the row and has a new line as the delimiter, but the dollar sign was lost:

=JOIN(CHILDREN(), CHAR(10)

I was able to fix by adding this: ="$" + JOIN(CHILDREN(), CHAR(10) + "$")

Just wanted to leave here in case anyone else had the same issue as I did.

Hello @Lisa McMunn

Try this:

Yard Access Only column:

=INDEX(COLLECT({Associate Name},{Yard Access Expiration},@cell<>"",{GTDR Expiration},@cell=""),1)

GTDR Certified:

=INDEX(COLLECT({Associate Name},{Yard Access Expiration},@cell<>"",{GTDR Expiration},@cell<>""),1)

The number column would go on the Permissions sheet.