+ Reply to Thread
Results 1 to 10 of 10

Update cell based on criteria in 2 columns

  1. #1
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Update cell based on criteria in 2 columns

    The attached file has column M and AL and AM. I want to automate what to categorize column M based on what contents columns AL and AM have...

    1. If AL and AM both have numbers - less than or greater than zero - than populate the corresponding cell in column M with "NILL/PIM" (without the ")
    2. If only AL has numbers - less than or greater than zero - than populate the corresponding cell in column M with "Standard"
    3. If only AM has numbers - less than or greater than zero - than populate the corresponding cell in column M with "PIM"
    4. If both AL and AM have a zero, than leave corresponding cell in column M empty , no category...

    Note: the cells where there is no value less than or greater than zero, there will be a zero populated...
    Attached Files Attached Files
    Last edited by rz6657; 02-19-2018 at 08:23 PM. Reason: Changed the condition; my error

  2. #2
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Update cell based on criteria in 2 columns

    Hi, rz6657!

    Try this:
    [M8] : =IF(COUNT(AL8:AM8)=2,"NILL/PIM",IF(COUNT(AL8),"Standard",IF(COUNT(AM8),"PIM","")))

    Blessings!

  3. #3
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Update cell based on criteria in 2 columns

    I forgot 1 more condition:

    4. If both AL and AM have a zero, than leave corresponding cell in column M empty , no category...

    thx

  4. #4
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Update cell based on criteria in 2 columns

    Quote Originally Posted by rz6657 View Post
    I forgot 1 more condition:

    4. If both AL and AM have a zero, than leave corresponding cell in column M empty , no category...

    thx
    This condition already cover in post #2. Blessings!

  5. #5
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Update cell based on criteria in 2 columns

    I updated the conditions to better show what I need and added Type1 file to reflect what I see when I use the formula in post 2... thx

  6. #6
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Update cell based on criteria in 2 columns

    I tried changing the formula but unable to make it work. I could just remove the zeros where the value is greater or less than zero, but the zeros have to be in there...

  7. #7
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Update cell based on criteria in 2 columns

    Hi, again!

    Try this new formula with your new conditions:
    [M8] : =IF(COUNT(AL8:AM8),IF(COUNTIF(AL8:AM8,"<>0")=2,"NILL/PIM",IF(AL8<>0,"Standard","PIM")),"")

    Blessings!

  8. #8
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Update cell based on criteria in 2 columns

    GM Johnmpl

    First, thanks for your help, it's really appreciated. The formula you just provided works well except for condition #4. When both AL and AM are zero, it's returning "PIM" vs leaving it blank... any ideas why?

  9. #9
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Update cell based on criteria in 2 columns

    Ok... try this one, please:

    [M8] : =IF(COUNT(AL8:AM8),IF(COUNTIF(AL8:AM8,"<>0")=2,"NILL/PIM",IF(AL8<>0,"Standard",IF(AM8<>0,"PIM",""))),"")

    Blessings!

  10. #10
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    362

    Re: Update cell based on criteria in 2 columns

    Thanks Johnmpl, that worked perfectly---

+ 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. [SOLVED] Update cell based on several criteria using VBA
    By rz6657 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-25-2017, 09:42 AM
  2. [SOLVED] Update cell based on several IF criteria
    By rz6657 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-24-2017, 07:41 PM
  3. [SOLVED] Update Columns to Another Worksheet, then update another worksheet based on a set criteria
    By mnunnley in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-09-2016, 11:31 AM
  4. How to extract values from a cell into multiple columns based on criteria
    By Totemus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-28-2016, 01:35 PM
  5. Delete columns based on cell criteria
    By enhydra in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-31-2013, 11:39 PM
  6. [SOLVED] Return cell value based on criteria of 2 columns
    By Fatnslow in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-03-2012, 04:52 AM
  7. [SOLVED] Shade Columns based on criteria in cell.
    By Bill in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-19-2005, 04:05 PM

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