I am struggling with my countifs formula

I am trying to use a countifs formula in my sheet summary to compile some information. This is a failure log in our internal product testing. I want to see how many of a specific model have failed due to each of these reasons: Assembly, Materials, Process, or Other reason. So how many Hydra failed due to Assembly, Due to Materials, etc.

I think one issue is that I want all the instances of Issues accounted for.

Here is my formula: =COUNTIFS(Model:Model, CONTAINS("Hydra"@cell),[Issue 1 Failure Type]:[Issue 6 Failure Type],"Assembly)

Error cord: #unparseable

We have 3 types of Hydra, but for my proposes I don't need to break the data down into type of hydra, just if a hydra failed (or if any of our other models failed for one of the above reasons).

Find more posts tagged with

Accepted answers

Andrée Starå

Hi @kstaver

I hope you're well and safe!

Try something like this. (the ranges have to match in length)

=COUNTIFS(Model:Model, CONTAINS("Hydra", @cell), [Issue 1 Failure Type]:[Issue 1 Failure Type], "Assembly") + COUNTIFS(Model:Model, CONTAINS("Hydra", @cell), [Issue 2 Failure Type]:[Issue 2 Failure Type], "Assembly") + COUNTIFS(Model:Model, CONTAINS("Hydra", @cell), [Issue 3 Failure Type]:[Issue 3 Failure Type], "Assembly") + COUNTIFS(Model:Model, CONTAINS("Hydra", @cell), [Issue 4 Failure Type]:[Issue 4 Failure Type], "Assembly") + COUNTIFS(Model:Model, CONTAINS("Hydra", @cell), [Issue 5 Failure Type]:[Issue 5 Failure Type], "Assembly") + COUNTIFS(Model:Model, CONTAINS("Hydra", @cell), [Issue 6 Failure Type]:[Issue 6 Failure Type], "Assembly")

Did that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@kstaver

Excellent!

Yes, I type it in Smartsheet or an editor called, TextSoap (Mac).

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Andrée Starå

Hi @kstaver

I hope you're well and safe!

Try something like this. (the ranges have to match in length)

Did that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

kstaver

That worked! I was trying to get away from needing to specify each row individually.

Do you use a text editor or a different tool to write out long strings of formulas?

Andrée Starå

@kstaver

Excellent!

Yes, I type it in Smartsheet or an editor called, TextSoap (Mac).

kstaver

Thanks!

Andrée Starå

@kstaver

You're more than welcome!

kstaver

@Andrée Starå

I have one more question. Working on the same sheet. If I want to total up the number if issues per row. What is the best formula for that?

Thanks for all your help. I have the most beautiful dashboard now tracking our Testing process.

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.