I need to filter the data to figure out which product is failing the most? formula help?

Amycham

smartsheet.png

I am required to break down the data into several categories including which product has failed, how many times it has failed.

I want to connect the Part number with the Pass/fail box, what is the formula for this?

KPH

I would use the COUNTIFS formula (see https://help.smartsheet.com/function/countifs)

The screenshot is very small and I can't make out your field headings. This is my replica (you will need to change the column names if I haven't got these right).

You can then create COUNTIFS like this

for Part Number 1 Pass

=COUNTIFS([Part Number]:[Part Number], "1", [Pass Fail]:[Pass Fail], "Pass")

for Part Number 1 Fail

=COUNTIFS([Part Number]:[Part Number], "1", [Pass Fail]:[Pass Fail], "Fail")

But rather than creating all those formula for each I would create a table and reference the part numbers from that, for example:

The image shows the formula nicely color coded and in context. Here it is to copy/paste:

=COUNTIFS([Part Number]:[Part Number], $[Column4]@row, [Pass Fail]:[Pass Fail], [Column6]$11)

The same formula is used in all the yellow cells so you can set up hundreds of part numbers in an instant. You will need to change the $[Column4] and [Column6] to your own column names (keep the $ for the column containing the part numbers) and the $11 to the row number you have the headings in.

You may want (probably should) put this summary on an entirely different sheet. In that case, you can cross reference the original sheet (changing the references in blue and green). See https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

KPH

Hi, @Amycham did the COUNTIF formula work for you and the explanation make sense? If so, please mark the answer as Accepted to help future learners locate the information or let other helpers know this is solved.