Need help with a formula

The formula below works as is to find the 4 specified values. Now I need help rewriting it so that it only counts rows where the "Priority Type/Level" column contains the value "1_Tier 1: Priority Project" in addition to the conditions i specified for the values - only "SPK", "LS", "IPS UK", and "Other" in a multi select column with 4 options to select, that can be selected in about 15 possible combinations. I realize I will have to write a formula for each possible option, but if I can get help with one, I think I can figure out the rest. Thanks in advance!!!

here's the current formula:

=COUNTIFS(Platform:Platform, AND(HAS(@cell, "SPK"), HAS(@cell, "LS"), HAS(@cell, "IPS UK"), HAS(@cell, "Other"), COUNTM(@cell) = 4))

here's a screenshot of the sheet:

Find more posts tagged with

Accepted answers

All comments

Gillian C

Hi @Seatora

I think you need

=COUNTIFS(Platform:Platform, AND(HAS(@cell, "SPK"), HAS(@cell, "LS"), HAS(@cell, "IPS UK"), HAS(@cell, "Other")), [Priority Type/Level]:[Priority Type/Level], "1_Tier 1: Priority Project")

I removed the Countm(@cell)=4) as I wasn't sure what it was accomplishing and it appeared to work without it?

Seatora

Hi Gillian! Thank you so much for responding! It seemed to continue to work for those 4 values, but when I adjusted the formula for the next 3-value summary it didn't work. Any idea why?

Gillian C

Hi @Seatora

My understanding of your formula is that it is showing the correct count.

From the columns Priority Type/Level and Platform, there are only 2 rows that have both "1_Tier 1: Priority Project" and "LS", "IPS UK" and "Other".

Are you needing the formula to count the rows where "1_Tier 1: Priority Project" occurs and ONLY "LS", "IPS UK" and "Other"?

(At the minute your formula is counting the two green rows under the 9_Completed row.)

Updated: If you need the formula to count the rows where "1_Tier 1: Priority Project" occurs and ONLY "LS", "IPS UK" and "Other" then try

=COUNTIFS(Platform:Platform, AND(HAS(@cell, "LS"), HAS(@cell, "IPS UK"), HAS(@cell, "Other")), Platform:Platform, NOT(HAS(@cell, "SPK")), [Priority Type/Level]:[Priority Type/Level], "1_Tier 1: Priority Project")

If that isn't what you are needing then @ me and then I'll get a notification :)

Seatora

Thank you again for jumping in here! :)

Going back to give you a little more context - I'm trying to create formula that can (within 1 multi-select column) count a specific value or any combination of a set of values. (The list of 4 options are "SPK", "LS", "IPS UK", and "Other", and at the moment there are 15 possible combinations.) These may also appear in any order within each cell. The second component to the problem is that I need 2 versions of this set of formulas. I will inevitably have to add more options to the list later on, so the solution must be "adjustable" to accommodate the changes. ANY help you can provide will be very much appreciated!!!

Version 1 should count only any of those options but only of they have the value "1_Tier 1: Priority Project" in the "Priority Type/Level" column.

Version 2 should count only any of those options but only of they have the values listed below in the "Priority Type/Level" column:

1_Tier 1: Priority Project

2_Tier 2: Priority Project

3_Tier 3: Unprioritized

7_Parked

8_Cancelled

9_Completed

Gillian C

Hi @Seatora,

I don't think you can actually do that in a formula within one column? @Genevieve P. do you know if the above possible?

As an alternative have you thought about creating a 'Row Report'?...it might give you more of the flexibility that you need? But again would need setting up for multiple combinations.

Another option if you have the software available to you is to pull the smartsheet data into Power BI and create a report through PowerBI. This would let the user filter by Tier or combination of Platforms? (I think if I'm understanding what you are asking, Power BI would be my go to in this instance). I'm interested to understand if Genevieve P. has any suggestions.

Seatora

Unfortunately, I don't have access to power BI. I'm interested in the row report idea, but I'm not sure how to set it up to make it work. These 2 formulas I'm working on are simply to power 2 separate widgets on a dashboard, that show what levels of projects affect each of the "platforms", as well as to be able to further quantify where there is overlap between work on those platforms. I've seen where a helper column might be an option, but I'm not sure how to go about setting one up in regards to what I would want to calculate there. I do know that I could list all of the possible options out in a single select list - but it looks cleaner in the multi-select list.

REALLY appreciating the help on this!!!!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.