Need a formula to compare 3 columns and return value

Swetha

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

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:

https://help.smartsheet.com/function/contains