Countif Problem

[grlinden]

I have part numbers in columns B,F,J,N,R each from a different assembly. I want to see if the part numbers are duplicated in any of the other four assemblies. In column D (for the first assembly) I have this formula... "=IF((LEFT(RC[-2],4)=""BLAN""),0,(IF(COUNTIF(R6C[2]:R200C[2]:R6C[6]:R200C[6]:R6C[10]:R200C[10]:R6C[14]:R200C[14],RC[-2]),1,0)))". This returns a 1 if the part number isn't "Blank" and it is used in one of the for other assemblies. Then for assembly 2 I have this formula in column H... "=IF((LEFT(RC[-2],4)=""BLAN""),0,(IF(COUNTIF(R6C[-6]:R200C[-6]:R6C[2]:R200C[2]:R6C[6]:R200C[6]:R6C[10]:R200C[10],RC[-2]),1,0)))". The problem is this always returns a 0 even if the part number is duplicated in one of the four other assemblies. It works however when I change the formula to only check part numbers from the first assembly. Any help would be great.

 

[acw]

Hi

Seemed to work ok for me. However, it was including column H in the countif as the range spanned columns B:R inclusive.

Tony