Nested IF and COUNTIF function.

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,sans-serif;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-fareast-language:EN-US;}

I’m using three different functions like this to reach a result.

Cell          Formula

A3    =IF(COUNTIF(A2:L2;A1)>1;A1;””)

A5    =IF(COUNTIF(A4:L4;A3)=1;A3;””)

A7    =IF(COUNTIF(A6:L6;A5)=0;A5;””)

(Using Italian keyboard, seems it’s “;” instead of “,”)

Can anyone be kind enough to show a way to combine these three formulas into one.  So, it will give a output like this:

IF; A1   is  (between range A2:L2) appeared more than 1 times and also  A1 is (between range A4:L4) appeared only 1 time and also A1 (between range A6:L6)  appeared 0 time; then the value is True (A1)  else the value is False (“”)

It will be a great help and will save a lot of time.

Thanks in advance.

By: Abul

Leave a Reply

Your email address will not be published. Required fields are marked *