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

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:

Year (=YEAR(Created@row)
Month (=MONTH(Created@row)
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?

Find more posts tagged with

Formulas

Countif

Accepted answers

All comments

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! :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.