Does anyone know if the CONTAIN formula is the correct formula to use?

sortiz2022

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.

Kelly Moore

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

sortiz2022

@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.

Kelly Moore

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