Struggling with Formula...not a qualified programmer!!!

"In sheetname ??? If column Risk Score is between 0 to 5 and Risk/Item column contains "Risk" then count how many risk are between 0-5"

This is what I tried and did not work.....

"=COUNTIFS({BT RAID Log Range 1} Risk Score, ">0", Risk Score, "<=5", Risk/Item, "*Risk*")"

Can someone please advise on how to create this formula in Smartsheet????

Struggling when there are no decent YouTube channels on creating formulas.

Call me on +44 07930 508 813

Find more posts tagged with

Accepted answers

All comments

Danielle Arteaga

I'm going to assume based on your description that your data is organized something like this:

If you place this formula:

=COUNTIFS([Risk Score]:[Risk Score], >=0, [Risk Score]:[Risk Score], <=5, [Risk/Item]:[Risk/Item], "Risk")

In the first cell of the column Total Risks, you get the count of 4, which is the total number of Risk Scores that are greater than or equal to 0 but less than or equal to 5 AND that are categorized as "Risk" in the Risk/Item column.

If the columns you are trying to count are on a different worksheet than the worksheet you are using to count the relevant results, you would need to use a cross-sheet reference. You can learn more about these here: https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

You can also find a lot of helpful information about working with formulas in Smartsheet here: https://help.smartsheet.com/topics/formulas-and-functions

rakmis

I am trying to create formula for this scenario..

are Formulas correct....?

=COUNTIF({BT RAID Log Range 1}), >=0, [Impact]:[Impact], <=2, [Risk/Issue]:[Risk/Issue], "Issue") (LOW)

=COUNTIF({BT RAID Log Range 1}), >=3, [Impact]:[Impact], <=4, [Risk/Issue]:[Risk/Issue], "Issue") (MEDIUM)

=COUNTIF({BT RAID Log Range 1}), >=5, [Impact]:[Impact], [Risk/Issue]:[Risk/Issue], "Issue") (HIGH)

Genevieve P.

Hi @rakmis

There are a few small tweaks you would need to do to this formula:

1 - Use CountifS plural because you are looking for multiple criteria

2 - You have a {cross sheet reference} and a reference to your column in your sheet. I believe you only need the one reference, so change {BT RAID Log Range 1} to [Impact]:[Impact],

Try:

=COUNTIFS([Impact]:[Impact], >=0, [Impact]:[Impact], <=2, [Risk/Issue]:[Risk/Issue], "Issue")

Cheers,

Genevieve

rakmis

what does this "[Impact]:[Impact]," ...I don't understand!

Genevieve P.

Hi @rakmis

The words in [these] are the column names. I can't see the column names in your example so I was going off of what you have here:

However in Smartsheet these would need to be here:

Then if you're building a formula in the same sheet, you'll reference that column by using its name. You can do this by clicking on any cell in that column to bring it into the formula:

In your case, you want to reference the entire column, so you'll want to reference the column name twice with a colon between, like so:

The process is different if you're using cross-sheet formulas (if you're writing the formula in a second sheet).

Here are two free webinars that you may find helpful as you learn about formulas:

https://help.smartsheet.com/learning-track/best-practice-webinars/formula-foundations

Cheers,

Genevieve

rakmis

Thank you so much...now this makes a lot more sense!!! :)

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.