Formula not showing correctly

**BROOF** · 12-03-2012, 01:09 PM

When i run a formula(=IF(COUNTIF(Sheet2!A:A,Sheet1!B1)=1,IF(Sheet1!C1=1,"NEEDS 2B Removed","ACTIVE"),"")) it isnt showing correctly because the value it is looking up (Sheet1!B1) is in the separate spreadsheet9Sheet1!C) 2+ times. Is there any way around this?

I have attached a sample of what the issue is.

Thanks,

**kbkumar** · 12-03-2012, 01:17 PM

Originally Posted by BROOF

... it isnt showing correctly because the value it is looking up (Sheet1!B1) is in the separate spreadsheet9Sheet1!C) 2+ times.

Not sure if I understand this part. Try the below in Sheet 1:

Formula:

Please Login or Register  to view this content.

Seems to work?

**BROOF** · 12-03-2012, 01:28 PM

sorry should explain better, the value in the cell B1 is listed twice in A:A and from what i can gather than is making the formula show incorrectly.

In the attached file A1 should show "NEEDS 2B Removed" instead of showing Blank, which means that the formula is calculating FALSE instead of True.

Does this help?

**Fotis1991** · 12-03-2012, 02:25 PM

Your formula is this.

=IF(COUNTIF(Sheet2!A:A;Sheet1!B1)=1;IF(Sheet1!C1=1;"NEEDS 2B Removed";"ACTIVE");"")

In sheet2!A:A, number 1, exists 2 tImes. A1 &A13. so correctly does not show "NEEDS 2B Removed" as result.

**BROOF** · 12-03-2012, 02:32 PM

is there special characters or additional info i would need to add to make the formula function so if Sheet1!B1 is found in Sheet2!A:A no matter how many times then the second hlf of the formula would work(IF(Sheet1!C1=1;"NEEDS 2B Removed";"ACTIVE");""))

Does this make sense?

**Hercules1946** · 12-04-2012, 02:28 PM

Hello Broof
The problem is that COUNTIF must return 1 for the second IF to become operative. A1 is blank because COUNTIF returns 2
Try this in Sheet1("A1"):

=IF(COUNTIF(Sheet2!$A:$A,B1)>0, IF(C1=1, "NEEDS 2B Removed", "ACTIVE"),"")

HTH