If then

[PAL]

I have a multi column (10), multi row(1000s) table. I am trying the
following formula, with if two conditions are true.

=IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368<>""), "Compliant",
"Non-Compliant").

It doesn't seem to work. Ideas.

Thanks.

 

[Glenn]

I have a multi column (10), multi row(1000s) table. I am trying the
following formula, with if two conditions are true.

=IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368<>""), "Compliant",
"Non-Compliant").

It doesn't seem to work. Ideas.

Thanks.

Is this what you are looking for?


=IF(INDEX(Raw!$R$2:$R$2368,MATCH(F2,Raw!$D$2:$D$2368,0))<>"",
"Compliant","Non-Compliant")

 

[Gary Brown]

Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR
do you want to test each row for Compliance?

 

[PAL]

Sorry should have asked better. What I am trying to do is....

Look at all the records for each individual (F2),and ask if there is a date
in the column? If there is a date in all the records (ie 10/10) for the
individual (F2), then I want to put in "compliant", if only (ie 9/10) have
dates), then "non-compliant"

 

[PAL]

No, the first few are NA, the rest are compliant. There is a random
assortment. Thanks for trying.

 

[Glenn]

Array formula (commit with CTRL+SHIFT+ENTER):

=IF(MIN(IF(Raw!$D$2:$D$2368=F2,
--(Raw!$R$2:$R$2368<>""),""))=1,
"","Non-")&"Compliant"

OR

=IF(SUM(--(A1:A21=D4))=
SUM((A1:A21=D4)*(B1:B21<>"")),
"","Non-")&"Compliant"

 

[Glenn]

Forgot to correct the cell references in the second one...should have been this:

=IF(SUM(--(Raw!$D$2:$D$2368=F2))=
SUM((Raw!$D$2:$D$2368=F2)*(Raw!$R$2:$R$2368<>"")),
"","Non-")&"Compliant"

 

[PAL]

No Dice. It comes back as all compliant. I did I did modify your formulas
to reference the correct cells. In order to verify your formulas, I created
this one in an adjacent cell...

=SUMPRODUCT((Raw!$D$2:$D$2468=B8)*(Raw!$N$2:$N$2468<0)). This will give me
a number. If the number is 0, I count it as compliant.

Here is yours with the modified reference.

=IF(MIN(IF(Raw!$D$2:$D$2368=B8,--(Raw!$R$2:$R$2368<>""),""))=2,"","Non-")&"Compliant"

I did the same for the second formula.

 

[Glenn]

Two things:

Did you enter both of mine as array formulas (commit with CTRL+SHIFT+ENTER)?

If your SUMPRODUCT gives you predictable results, can't you just wrap it in an
IF to give the desired output?

=IF(SUMPRODUCT((Raw!$D$2:$D$2468=B8)*(Raw!$N$2:$N$2468<0))<>0,"Non-","")&"Compliant"