Does anyone know if the CONTAIN formula is the correct formula to use?
This is my formula : =COUNTIFS({Requesting BU}, CONTAINS([Business Unit]@row, {Requesting BU}), {Final RR}, "Medium") / COUNT({Requesting BU})
I have a source sheet that contains a list of business units. One business unit has hundreds of branch offices so it will be too many to list. I'm trying to write a formula that essentially does the following: If the business unit is Marketing Branch 1, Marketing Branch 2, etc. I want the formula to count every instant where "Marketing Branch" appears regardless of the branch number attached. The other portion of the formula "Final RR, "Medium" is trying to find all business unit branches with a medium risk.
Thanks in advance.
Answers
-
Hey @sortiz2022
I'm happy to help. Will all of your Business units contain the words "Marketing Branch"? If no, Is it possible to get a screenshot of your data in that column so I can see the variability of the data?
If all Business Units do contain the words 'Marketing Branch', then your formula is this:
=COUNTIFS({Requesting BU}, CONTAINS("Marketing Branch", @cell), {Final RR}, "Medium") / COUNT({Requesting BU})
I'll wait for your reply
Kelly
-
@Kelly Moore Thank you for your reply. Yes, all of the branches will include "Marketing Branch," Actually they will include "PCG", which is the branch name. I tried the formula and it calculates, but it didn't produce the correct results and I think that's because I'm referencing another sheet. The @cell reference may need to link back to the sheet, right? Again, thank you for your assistance.
-
Hey @sortiz2022
Can you give me a screenshot of your formula? The @cell references the range - so if you correctly inserted the cross sheet reference range into your sheet, you are covered. The @cell says look at every cell in whatever range is referenced and evaluate the criteria, cell by cell.
Is it possible to give me screenshots of the two sheets without revealing sensitive information? What number is the current formula giving you, and what value were you expecting?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!