Need a formula to compare 3 columns and return value

Swetha
Swetha
edited 02/11/23 in Formulas and Functions

Source sheet has 3 columns Test, System, Impact. In Target sheet i need individual Impact value by comparing values in Test and System columns.

Formula is not working, it is considering blank count. i dont want blank to be considered: By the below formula blank is considered.

=COUNTIFS({Test}, "Sony", {Impact}, "Critical", {System}, NOT(CONTAINS("Test", (@cell))), {System}, NOT(CONTAINS("<SELECT>", (@cell)))),{System}, NOT(CONTAINS(" ", (@cell)))+COUNTIFS({Test}, "Samsung", {Impact}, "Critical", {System}, NOT(CONTAINS("Test", (@cell))), {System}, NOT(CONTAINS("<SELECT>", (@cell))),{System}, NOT(CONTAINS(" ", (@cell))))+COUNTIFS({Test}, "LG", {Impact}, "Critical", {System}, NOT(CONTAINS("Test", (@cell))), {System}, NOT(CONTAINS("<SELECT>", (@cell))),{System}, NOT(CONTAINS(" ", (@cell))))

Same formula is used for High and Low by replacing Critical.

Source sheet:

From Source sheet, System column : Either its Sony, Samsung or LG values has other than Test,<SELECT>,blank value needs to be counted in Target Sheet.

Value returned using above formula:


In Target Sheet i need values like below: Without considering blank,<SELECT> and Test


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Swetha

    The CONTAINS function is typically needed when you are trying to find a portion of a word or phrase, or you're finding values in a multiselect cell. The screenshots above do not indicate either situation. Thus, the formula you need can be simplified.

    =COUNTIFS({Test}, "Sony", {Impact}, "Critical", {System}, AND(@cell <> "Test", @cell <> "", @cell <> "<SELECT>")) + COUNTIFS({Test}, "Samsung", {Impact}, "Critical", {System}, AND(@cell <> "Test", @cell <> "", @cell <> "<SELECT>")) + COUNTIFS({Test}, "LG", {Impact}, "Critical", {System}, AND(@cell <> "Test", @cell <> "", @cell <> "<SELECT>"))

    Will this work for you?

    Kelly

    If may find this link helpful:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!