Check a table with part of a bigger table

[forexcel]

I have data at 200 cells in column A starting from cell A11 ending at cell A210. At column B there are data at 30 cells starting from B11 ending at B40. The cells of column B have a connection with cells of row A. Cells B11:B30 should be every 10 cells in column A, this means B11 should be one of A11:A20, B12 should be one of A21:A30 etc The remaining 10 cells of column B (B:31:B30) should be every 20 cells in column A, so B31 should be one of A11:A30, B32 one of A31:50 etc. So I want to calculate at B1 how many cells doesn't follow the first rule and at B2 how many cells doesn't follow second rule. Any idea which formula to use?

 

[oldbrewer]

A

B

C

this is row 10

red

green

#$$$###$

#$$$###$

=IF(ISERROR(MATCH(B11,A11:A20,0)),"fail","")

blue

white

fail

######

#####

=IF(ISERROR(MATCH(B12,A21:A30,0)),"fail","")

highlight C11:C12 and drag down to C30

green

red

pink

orange

blue

blue

yellow

yellow

cyan

black

fail

orange

cyan

red

green

blue

white

fail

green

red

pink

orange

blue

blue

yellow

yellow

cyan

black

fail

orange

cyan

red

green

blue

white

fail

green

red

pink

orange

blue

blue

yellow

yellow

££££££

££££££

=IF(ISERROR(MATCH(B32,A31:A40,0)),"fail","")

cyan

black

fail

$$$$$$

$$$$$$

=IF(ISERROR(MATCH(B33,A32:A41,0)),"fail","")

highlight C32:C33 and drag down to C40

orange

cyan

red

green

blue

white

fail

green

red

pink

orange

blue

blue

yellow

yellow

cyan

orange

red

<colgroup><col span="17"></colgroup><tbody>
</tbody>

row 44 onwards not shown to save space but is a repeating pattern

 

[forexcel]

Thanks for your reply but i need to do this without the use of a third column. I want a formula just to have a number in a cell with the sum of "fail"

 

[oldbrewer]

the third column can be out of sight, eg column EA the sum of column EA can be in any cell of your choosing....

 

[forexcel]

I know this but it is not just column B. There will follow plenty of other column at C,D etc at same format with column B (will be surely over 200). I used the follow method to calculate

=IF(COUNTIF(OFFSET($A$10;(ROW(B11)-10)*10+1;0):OFFSET($A$10;(ROW(B11)-10)*10+10;0);B11)>0;"OK";"Fail")

for each cell and it is working but didn't manage to add them all. I tried with SUMPRODUCT but didn't work. Probably did something wrong

 

[oldbrewer]

ok but C and D can be out of sight too....best of luck !