I am struggling with my countifs formula

kstaver

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

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!

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!

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

✅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!

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.