CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

Pugazh

New Member

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.

vletm

Excel Ninja
Pugazh
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:

Pugazh

New Member
Excellent. This works. Thank You very much.

bosco_yip

Excel Ninja
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

Pugazh

New Member
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.

bosco_yip

Excel Ninja
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.
1] How does =SUBTOTAL(103,E2:E3) return value as 5 ? Only 2 cells in E2:E3, should return 2.

Regards
Bosco

Pugazh

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

Attachments

• 10.8 KB Views: 4

bosco_yip

Excel Ninja
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

Pugazh

New Member
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.

bosco_yip

Excel Ninja
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:

Pugazh

New Member
Can anyone help on this please?

Pugazh

New Member
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))

Attachments

• 19.8 KB Views: 1

Pugazh

New Member
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))

bosco_yip

Excel Ninja
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))
View attachment 57640
Try,

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

Regards
Bosco

Pugazh

New Member
Thank You very much it works.