Which formula do I need "Has" or "Countifs"

LaraH

I'm having trouble knowing what type of formula to use in my dashboard, and how to get the results that I need. To preface - I am very new to Smartsheet, and the use of formulas so pretty much "learning as I go". What I have here is a result of combined YouTube videos and LinkedIn learning Smartsheet 101 videos. 

Let me explain what I have/trying to achieve and then hopefully someone could offer guidance. I'll try to keep it brief...

I've created a "Master Smartsheet" where I capture the data I want to calculate. This includes (amongst a lot of other data) one column that tracks "Central KPIs" (column type is "Dropdown list", with "multiple values" enabled), and another that captures which dept. the project relates to called "Area of focus" (Drop down list, single values only). 

Then I've created a separate Smartsheet for the "calculations" - this is the data I will draw into the Dashboard, once the formulas are correct. So, what I'd like to show is: 

How many times the "value" (the KPI) appears in the "Central KPI" column, based also on which department they're in.

So, for example, how many times does "KPI-1" show in the "Central KPI" column, and when the "Area of focus" = e.g. Corporate Development (or whatever that would be for other dept). I have this formula that works well - across sheets - and counts what I need to see. Which is: 

=COUNTIFS({2023/24 Strategic measurement/Tracking LIT KPI}, [KPI by area]@row, {2023/24 Strategic measurement/Tracking LIT Range 1}, "Corporate Development")

Which, for my understanding - basically says "Count if" the Master sheet (2023/24 Strategic measurement/tracking etc.) has any of the values (the list of KPIs) from "KPI by area" column, and where the department is "Corporate Development".

And this works fine, but the issue I have is if I select "multiple" options for the "Central KPI" column, it shows as no-data in the dashboard and "0" (for the KPI/area) in the calculation sheet. 

I had a read and maybe I need to use a "has" formula, instead of a "count if" formula, or as well as. But I can't seem to make that work - every time I do I either get "incorrect argument" or "Invalid" etc. 

In summary: I'd like to show the number of times a "KPI" is chosen in the "Central KPI" column, even if there's multiple selected in the same cell - when it's in a specific department (e.g. corporate development). 

Is this possible across Smartsheets? Thank you SO much for anyone who takes the time to read this...

David Tutwiler

My apologies, I missed that in your description. If that's the case, then you should just be able to modify your existing COUNTIFS without having to add another parameter. Something like this should do it:

=COUNTIFS({2023/24 Strategic measurement/Tracking LIT KPI}, CONTAINS([Central KPI]@row, @cell), {2023/24 Strategic measurement/Tracking LIT Range 1}, "Corporate development")

David Tutwiler

I think you need a combination of things. You do need the COUNTIF formula, but you need to wrap the column you're looking at with a CONTAINS to check multi-select columns. For your specific example it would look like:

=COUNTIFS({2023/24 Strategic measurement/Tracking LIT KPI}, [KPI by area]@row, {2023/24 Strategic measurement/Tracking LIT Range 1}, CONTAINS("Corporate Development", @cell))

This CONTAINS wrapper looks for 2 parameters. The first is what to search for ("Corporate Development") and then where to search for it. Here the @cell is telling the rest of the function that as it goes down the list of rows in your search to check each one for containing the phrase "Corporate Development".

Hope this gets you going. Great detail on the question, it helps a lot when you post all of the specifics.

LaraH

@David Tutwiler - thank you so much for replying! And your description is very clear. Much appreciated! I feel like this could be the solution, however I cannot seem to make it work (my error I'm certain!). I simply get "#Incorrect argument set" error message. 

I must just mentioned that the column where I need the multi-select identifying is the "Central KPI" column (of which there are 16 values to choose), not the department - or specifically "Corporate development". So I tried to edit your formula to match this based on the great logic you provided. I seem to have failed... 

Just so I know I'm being clear, I have two Smartsheets: 

- Master smartsheet entitled: "2023/24 Strategic measurement/Tracking LIT"

- Calculation smartsheet: where my formula "answers" are kept, so I can pull these through as % onto the dashboard. 

What I'm trying to find out is: 

- How many times each "KPI" appears in the "Central KPI column" (from the master smartsheet, in which they can choose 1 or more KPIs, up to 16) when the department is e.g. Corporate development (single choice, again a column that lives in the master smartsheet but could be 1 of 5 possible "depts", but only ever 1 at a time). 

SO I did: 

=COUNTIFS({2023/24 Strategic measurement/Tracking LIT KPI}, [Central KPI]@row, {2023/24 Strategic measurement/Tracking LIT Range 1}, "Corporate development",CONTAINS("KPI-1", {2023/24 Strategic measurement/Tracking LIT KPI}))

(FYI I've used "KPI-1" just as a pseudonym, for privacy sake. So imagine there's a list of KPI-1, KPI-2 etc. up to 16, like the image). 

This is what I imagine the final (correct) result to look like (this is the calculation sheet for the dashboard)

THANK YOU SO MUCH FOR YOUR HELP!

David Tutwiler

My apologies, I missed that in your description. If that's the case, then you should just be able to modify your existing COUNTIFS without having to add another parameter. Something like this should do it:

=COUNTIFS({2023/24 Strategic measurement/Tracking LIT KPI}, CONTAINS([Central KPI]@row, @cell), {2023/24 Strategic measurement/Tracking LIT Range 1}, "Corporate development")

LaraH

Oh my @David Tutwiler that works!! You are my hero.

I genuinely can't tell you how grateful I am that you took the time to read my war-and-peace-like plea! It's very much appreciated. 🙏

David Tutwiler

Of course. That's what the community is for. Eventually, you'll be answering other people's questions on here.