1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

Discussion in 'Ask an Excel Question' started by Pugazh, Jan 8, 2019.

  1. Pugazh

    Pugazh New Member

    Messages:
    19
    upload_2019-1-8_23-4-31.png
    in the above sheet values in A2:D6 will be either (NP, D2, D3 or OK). How to get automatically with formula to return a text in say column E2. Check cells (A2:D2) Condition1 is if any of the cell value is "NP" the value to be returned in is "NP". Condition2 if no "NP" then if there is D2 it should return D2. If none of the cells are with "NP" & "D2" if it has "D3" then return "D3". If all cells is with ÖK" then value to be returned is OK.
  2. vletm

    vletm Excel Ninja

    Messages:
    4,633
    Pugazh
    Please reread:
    https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/
    Please Don't ... the 1st sentence

    =IF(COUNTIF(A2:D2,"OK")=4,"OK",IF(COUNTIF(A2:D2,"NP")>0,"NP",IF(COUNTIF(A2:D2,"D2"),"D2","D3")))

    or
    Did You really mean ...
    If none of the cells are with "NP" & "D2" if it has "D3" then return "D3".
    then how next sentence can be possible?
    If all cells is with ÖK" then value to be returned is OK.
    Last edited: Jan 8, 2019
    Thomas Kuriakose likes this.
  3. Pugazh

    Pugazh New Member

    Messages:
    19
    Excellent. This works. Thank You very much.
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    Or….....................

    In E2, formula copied down :

    =INDEX({"NP";"D2";"D3";"OK"},AGGREGATE(15,6,MATCH(A2:D2,{"NP";"D2";"D3";"OK"},0),1))

    Regards
    Bosco
    Thomas Kuriakose likes this.
  5. Pugazh

    Pugazh New Member

    Messages:
    19
    Dear Bosco,
    Thanks. This works but i am not able to do subtotal of all the values returned. Means subtotal(103,E2:E3) = is showing blank. it should return value as 5.
  6. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    1] How does =SUBTOTAL(103,E2:E3) return value as 5 ? Only 2 cells in E2:E3, should return 2.

    2] Please forward us more information as well as a sample file.

    Regards
    Bosco
  7. Pugazh

    Pugazh New Member

    Messages:
    19
    Dear Bosco,
    Sorry. Typo error. E2:E6 is the range and value should return 5. Sample file attached. Please suggest.

    Attached Files:

  8. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    Or,

    you can use AGGREGATE function instead of SUBTOTAL fuction, which have a ignore error values behavior.

    In G8, enter :

    =AGGREGATE(3,6,G2:G7)

    Regards
    Bosco
    Thomas Kuriakose likes this.
  9. Pugazh

    Pugazh New Member

    Messages:
    19
    Dear Bosco,
    This work but does not work when working with filters. Means value should change when filter is applied or some rows are hidden.
  10. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    1] I don’t know why Subtotal can't work in Column G?

    2] I try to use a helper column. In H2, enter: =G2, and copied down to H6

    3] In H1, enter: =SUBTOTAL(103,H2:H6), and it is working when filter is applied.

    4] Maybe someone can help to give you an answer

    5] Sorry in unable to help.

    Regards
    Bosco
    Last edited: Jan 16, 2019 at 4:08 PM
  11. Pugazh

    Pugazh New Member

    Messages:
    19
    Can anyone help on this please?
  12. Pugazh

    Pugazh New Member

    Messages:
    19
    cell AZ3=SUBTOTAL(103,AZ8:AZ300) - returns value null.

    column AZ8:AZ300 has the following formula.
    =INDEX({"NP";"D2";"D3";"OK"},AGGREGATE(15,6,MATCH(AT8:AY8,{"NP";"D2";"D3";"OK"},0),1))

    upload_2019-1-17_21-32-18.png

    Attached Files:

  13. Pugazh

    Pugazh New Member

    Messages:
    19
    Dear Bosco,
    Subtotal returning null value is solved by using formula with Sumproduct/subtotal/offset formula.

    can you help in solving - if there is no entry or value in cells A7:F7 it returns #NUM!. cell G7 should be blank. Formula in cell G7 is =INDEX({"NP";"D2";"D3";"OK";""},AGGREGATE(15,6,MATCH(A7:F7,{"NP";"D2";"D3";"OK";""},0),1))
    upload_2019-1-17_22-0-45.png
  14. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    Try,

    =IF(COUNTA(A2:F2),INDEX({"NP";"D2";"D3";"OK"},AGGREGATE(15,6,MATCH(A2:F2,{"NP";"D2";"D3";"OK"},0),1)),"")

    Regards
    Bosco
    Thomas Kuriakose and Pugazh like this.
  15. Pugazh

    Pugazh New Member

    Messages:
    19
    Thank You very much it works.

Share This Page