Perform Count on "Year-Mon" extracted from Created Date

fornor1

I have a working sheet that utilises the Created Date functionality by Smartsheet (DD-MM-YY HH:MM).

I am looking to count the number of rows for every Month in a separate sheet to see new rows added over time.

to extract the year-month i have created 3 additional columns:

1. Year (=YEAR(Created@row)
2. Month (=MONTH(Created@row)
3. YEAR-MON (=JOIN(year@row:Month@row, "-")). Set as named range {Completed Year-Mon}

Small subset of rows shown below

In the metrics sheet i have added rows with the different Year/Months i am looking for wit the following formula, however the look up always returns just 1.

=COUNT(COLLECT({Completed Deal Name}, {Completed Year-Mon}, Label@row))

Both the YEAR-MON and the Metrics sheet are in Text/Number format.

Can someone help with this query?

fornor1

please ignore this question, after refreshing the page the formula worked

Jennifer Kurtz

Hi @fornor1 ,

Your COLLECT function is looking at the range "Completed Deal Name", with a criterion of the Completed Year & Month. Is that what you're intending to count, or are you looking only for a count of each year & month combo?

If you're looking only to count the year & month combos, you could use a COUNTIF - e.g., =COUNTIF({Completed Year-Mon}, Label@row)

Please let us know if that helps, or provide a bit more detail about your "Completed Deal Name" range.

Good luck!

fornor1

i am looking to count the Year and Month combo. i.e. how many rows were created in ech month of 2024

Example:

Jan 2024 (2024-1)

Feb 2024 (2024-2)

etc

The Deal Name is simply a non-blank column with free text to identify the name of the project/client.

i seem to have been using count (collect and countif as the same thing, if different could you explain/send me links to explain the delta? or include examples to ensure I understand

thanks

Jennifer Kurtz

@fornor1

Hello again!

You could write your formula using either "COUNT(COLLECT" or "COUNTIF" - but since your aim is to count the number of cells that meet a specified criteria, the COUNTIF function may be more straightforward.

The COUNTIF function allows you to count the number of cells in a range that meet one specified criterion. (If you have more than one criteria, you could use a COUNTIFS.) The syntax is COUNTIF(Range-You're-Counting, Criterion)

It sounds like you've since got your formula working, though, so that's great! :)