Formula help, please!

Alyson Harris

Hi Smartsheet Wizards,

I need to create a formula for all assets completed in the fiscal year, by region - I have created the formula below and it works perfectly, but now I need to narrow it to count just a subset of asset types (from a multi-select column). I can add a single content type and the formula still works, but I would love to know if I can create a single formula to capture this data. Any thoughts on how to add that? I need to find "Blog", "Case Study" and "Video" within our list of asset types.

Here is the formula that is working (I am referencing another sheet to create a roll-up):

=COUNTIFS({Status}, "Completed", {Geo}, "APJ", {Fiscal Year}, "FY2020")

Thank you!!

Alyson Harris

Hi there, I figured it out - might not be the most elegant solution, but it worked. I wrote the formula for each asset type, and joined them with a "+" and it worked!

Mark Cronk

Hi @Alyson Harris ,

You'll need to add a HAS function to your COUTIFS.

=COUNTIFS({Status}, "Completed", {Geo}, "APJ", {Fiscal Year}, "FY2020", {insert asset type range}, HAS(@cell, "insert value you want counted")="True"))

For more on HAS look here:

https://help.smartsheet.com/function/has

Work?

Mark

Alyson Harris

Thanks Mark - I already have working formula to pull in a single asset type, but what I am trying to do is count 3 asset types (from a list of 8 or so). I am not sure how to do that. From the range, I want to count only "blog" or "case study" or "video".

Any thoughts?

Alyson Harris

Hi there, I figured it out - might not be the most elegant solution, but it worked. I wrote the formula for each asset type, and joined them with a "+" and it worked!

Mark Cronk

Glad you found a solution. Thanks for contributing to the Community.

Mark