+ Reply to Thread
Results 1 to 5 of 5

Searching Multiple Columns to Calculate Severity

Hybrid View

  1. #1
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    34

    Searching Multiple Columns to Calculate Severity

    I have spreadsheet to assess risk in a given situation. There are multiple columns (AA-AO) that have different conditions, and if "y" is marked then it meets that condition. What I want to do is calculate if the person is "low," "medium," or "high risk" and display that in a cell.

    Low Risk columns AA-AE
    Medium Risk columns AF-AL
    High Risk Columns AM-AO

    If there is a "y" in one of the high columns, then I want to display "high," even if it also has a "y" in the low or medium columns, because I want the greatest risk to be displayed.

    I know how to do this individually for each set of columns, but I am sure that it is not very efficient. I don't know how to combine them or nest the formulas correctly. This may not be the most elegant or efficient solution, but this is as far as I have gotten.

    =IF(OR($AA4="y",$AB4="y",$AC4="y", $AD4="y",$AE4="y"),"y","")
    =IF(OR($AF4="y",$AG4="y",$AH4="y",$AI4="y",$AJ4="y",$AK4="y",$AL4="y"),"y","")
    =IF(OR($AM4="y",$AN4="y",$AO4="y"),"y","")

    I would so appreciate any help with this.
    Attached Files Attached Files

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Searching Multiple Columns to Calculate Severity

    Formula for AS4:

    =IF(COUNTIF($AM4:$AO4,"y")>0,"High",IF(COUNTIF($AF4:$AL4,"y")>0,"Medium",IF(COUNTIF($AA4:$AE4,"y")>0,"Low","")))
    Copy down as necessary.

    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    34

    Re: Searching Multiple Columns to Calculate Severity

    Thank you so, so much for your quick response! You are a lifesaver!

    As a bonus question... Is there a way to add something to the formula where, if there was nothing in any of the fields (AA4-AO4) that it would return "none"? I did not think of that before.

  4. #4
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Searching Multiple Columns to Calculate Severity

    =IF(COUNTIF($AM4:$AO4,"y")>0,"High",IF(COUNTIF($AF4:$AL4,"y")>0,"Medium",IF(COUNTIF($AA4:$AE4,"y")>0,"Low","None")))
    WBD

  5. #5
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    34

    Re: Searching Multiple Columns to Calculate Severity

    Thank you so much! I spent a long time trying to figure this all out, and you saved me a lot more time. Thank you for sharing your knowledge!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Searching over multiple columns
    By dode in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-26-2015, 08:21 AM
  2. Searching multiple columns
    By testsubjectzz in forum Excel General
    Replies: 7
    Last Post: 02-17-2015, 09:34 PM
  3. [SOLVED] Sorting by Severity
    By CorvusReed in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2014, 01:57 PM
  4. [SOLVED] Searching for multiple terms in multiple cell columns
    By damianrichman in forum Excel General
    Replies: 4
    Last Post: 03-18-2014, 11:47 PM
  5. Searching multiple columns
    By Erenagh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2011, 01:31 PM
  6. Searching multiple columns
    By BRISBANEBOB in forum Excel General
    Replies: 1
    Last Post: 12-19-2008, 03:47 AM
  7. [SOLVED] Searching multiple columns
    By Robin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-12-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1