• 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...

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

If (And Formula 64 Nesting Issue Need To Shorten Formula

Hussain_0929

New Member
Greetings
My requirements are:
formula cell is G16
I'm trying to assign auto numbering according to discipline cell(H16) and building cell(J16) criteria. It is working but exceeding 64 nesting. Need to shorten this formula. Need support. I attached the sheet

=IF(H16="","",IF(AND(J16="1BS-02",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BS-04"),IF(AND(J16="1BS-02",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS-02"),IF(AND(J16="1BS-03",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS-03"),IF(AND(J16="1BS-04",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BS-04"),IF(AND(J16="1BN-02",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"1BN-04"),IF(AND(J16="1BN-02",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN-02"),IF(AND(J16="1BN-03",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN-03"),IF(AND(J16="1BN-04",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"1BN-04"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="CIV"),COUNTIFS($H$14:H16,"CIV",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ELE"),COUNTIFS($H$14:H16,"ELE",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="MEC"),COUNTIFS($H$14:H16,"MEC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="STR"),COUNTIFS($H$14:H16,"STR",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="ARC"),COUNTIFS($H$14:H16,"ARC",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"),IF(AND(J16="BRT",H16="PLU"),COUNTIFS($H$14:H16,"PLU",$J$14:J16,"BRT"))))))))))))))))))))))))))))))))))))))))))))))))))))))))
 

Attachments

Top