If then
 

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.

If then
 

PAL wrote:
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$23 68,0))<"",
"Compliant","Non-Compliant")

If then
 

Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR
do you want to test each row for Compliance?
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"PAL" wrote:

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.

If then
 

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"

"Gary Brown" wrote:

Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR
do you want to test each row for Compliance?
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"PAL" wrote:

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.

If then
 

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

"Glenn" wrote:

PAL wrote:
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$23 68,0))<"",
"Compliant","Non-Compliant")
.

If then
 

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"


PAL wrote:
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"

"Gary Brown" wrote:

Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR
do you want to test each row for Compliance?
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"PAL" wrote:

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.

If then
 

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"



Glenn wrote:
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"


PAL wrote:
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"

"Gary Brown" wrote:

Are you trying to fill a SINGLE cell with 'Compliant' or
'Non-Complient' OR do you want to test each row for Compliance?
--
Hope this helps. If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"PAL" wrote:

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.

If then
 

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$246 8<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" wrote:

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"



Glenn wrote:
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"


PAL wrote:
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"

"Gary Brown" wrote:

Are you trying to fill a SINGLE cell with 'Compliant' or
'Non-Complient' OR do you want to test each row for Compliance?
--
Hope this helps. If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"PAL" wrote:

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

If then
 

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"

PAL wrote:
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$246 8<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" wrote:

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"



Glenn wrote:
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"


PAL wrote:
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"

"Gary Brown" wrote:

Are you trying to fill a SINGLE cell with 'Compliant' or
'Non-Complient' OR do you want to test each row for Compliance?
--
Hope this helps. If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"PAL" wrote:

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