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
Answers
-
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
-
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)
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
what does this "[Impact]:[Impact]," ...I don't understand!
-
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:
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much...now this makes a lot more sense!!! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!