Problems with Formula: COUNTIFS to not include certain text

Hello.

I am trying to create a formula that helps me to summarize the information on my master sheet so I can create a dashboard. I am trying to sum up the number of courses between published and archived and the formula would collect data from two different columns (status and product type). Is it possible?

I tried to create the formula using Countif and Contains. Here's an example:

=COUNTIF({BlueSky Courses for IE Competencies Range 4}, "Published", CONTAINS({BlueSky Courses for IE Competencies Range 3}, [Primary Column]@row, @cell))

Logic (in my head only): Count the cell values "Published" in the Status column if contains (or belongs to) the product type "e-Learning Courses".

I'd love to hear also if there is any easier way to collect this information for the dashboard. Thank you so much for your help!

BlueSky Courses for IE Competencies Mater Sheet photo sample, and the new summary I am creating.

Find more posts tagged with

Formulas

Accepted answers

Mike TV

@Michael Pereira

If COUNTIF were the correct formula to use for what you want, you've got the syntax incorrect. COUNTIF will only allow for one range and one criteria. Your syntax is incorrect for COUNTIFS as well. Try this:

=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 3}, [Primary Column]@row)

This should work if Range 4 is the Status column and if Range 3 is the Product Type column.

All comments

Mike TV

@Michael Pereira

=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 3}, [Primary Column]@row)

This should work if Range 4 is the Status column and if Range 3 is the Product Type column.

Michael Pereira

That's so helpful Mike. I was able to apply that to my sheet, thank you so very much!

Are we able to use more than one function within the same formula? Within the same sheet, I am trying to create a formula where it can count the number of topics we have (i.e. Business Acumen) within the criteria of countif the course is published and is considered a course (i.e. [Primary Column]16). I've used the same formula and tried to add the function contains (which worked with a single countif function).

Here's what I am trying but I am getting an error:

=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 3}, [Primary Column]16, CONTAINS({BlueSky Courses for IE Competencies Range 2}, "Business Acumen", @cell))

I've used the Contains function because of that column has a multiple-choice selection.

Thank you again for your help!

Mike TV

@Michael Pereira

Your problem, it looks like to me, is that you are threading the CONTAINS function into the formula and you don't need to. You just list a range and then "Business Acumen" in quotes already tells SmartSheet to look in that range for a cell which contains "Business Acumen". Like this:

=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 3}, [Primary Column]16, {BlueSky Courses for IE Competencies Range 2}, "Business Acumen")

Also, I haven't seen your sheet but I believe you'll probably want to change [Primary Column]16 to [Primary Column]@row, like this:

=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 3}, [Primary Column]@row, {BlueSky Courses for IE Competencies Range 2}, "Business Acumen")

Michael Pereira

Thank you so much for your prompt support Mike, you are correct in suggesting I use just the COUNTIFS function and it worked well if the column with topics was single-choice. I think the formula is reading only the cells that precisely match the topic instead of "read all the options within one cell". I am adding the screenshot of the master list from which I am getting this info.

This formula you suggested worked well, but it's getting me back a 0. This is a huge progress compared to ERROR haha so thank you for that.

=COUNTIFS({BlueSky Courses for IE Competencies Range 4}, "Published", {BlueSky Courses for IE Competencies Range 3}, "e-Learning Course", {BlueSky Courses for IE Competencies Range 2}, [Primary Column]@row)

I wonder if there's a way to tell Smartsheet to look for any key work within a cell (multiple-choice option).

Status = BlueSky Courses for IE Competencies Range 4

Product Type = BlueSky Courses for IE Competencies Range 3

Competency = BlueSky Courses for IE Competencies Range 2

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.