Counting Specific Items within an array where row meets criteria

Hello,

I am trying to count specific text within and array: Basically, I am trying to count the number of times Testing occurred in the last 2 weeks, so using either a specific date or the check box. I will also expand this to specific people that is not shown in the screen shot.

I have the formula to count the array, but as soon as I add in the criteria for specific date or person it no longer works. I have tried both Count(collect()) and Countifs(). Trying to figure out what little piece I am missing.

=COUNTIFS([Period 1]:[Period 22], "Test Planning", [Last 2 Weeks]:[Last 2 Weeks], true)

Find more posts tagged with

Formulas

Accepted answers

All comments

Anjanesh Vaidya

Hi @Matt_A,

Since you are evaluating multiple columns for the first condition (Period 1 through Period 22), you can use two COUNTIF functions and add the results together to achieve the desired output.

Please use the formula below:

=COUNTIF([Period 1]:[Period 22], "Test Planning") + COUNTIF([Last 2 Weeks]:[Last 2 Weeks], true)

Shaz

Add one helper column and use =countifs([Period1]@row:[Period22]@row, "Test Planning" as colum formula and then use

=sumif(Last Week:Last Week, 1, [helper column]

amarkumar

@Matt_A

1.) You need to use two COUNTIF functions.
2.) then Add the outputs.

Updated formula below:

= COUNTIF([Period 1]:[Period 22], "Test Planning")
+
COUNTIF([Last 2 Weeks]:[Last 2 Weeks], true)

Paul Newcome

Using the two COUNTIFs will not give an accurate count.

My suggestion would be the helper column route where you count them on a row by row basis then add the helper column.

=COUNTIFS([Period 1]@row:[Period 22]@row, @cell = "Test Planning")

Then SUMIFS to add all of the appropriate rows:

=SUMIFS([Helper Column]:[Helper Column], [Last 2 Weeks]:[Last 2 Weeks], @cell = true)

Matt_A

I don't think that Smartsheet is able to do advanced analysis on arrays yet. To get the specifics I need without doing several dozen separate reports or hundreds of unique formulas, I ended up with a report to narrow down time period then DataMesh and 22 column formulas to uniquely identify each category to a specific person.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.