Need a formula to compare 3 columns and return value
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!