counting problem

[mickreid]

hi

i am trying to get a function that will count if a number out of a row of 10 different numbers has appeared in a range of numbers in a different part of the sheet

regards

Michael

[NBVC]

Can you give an example or 2 of what you mean?

[mickreid]

sorry that would help

i have added the file

basically i want to know if a number has appeared in the range shaded yellow has also appeared in the row so if i had the numbers 1 to 10 in my row and the numbers 1 to 7 had came out in the yellow range then it would return the number 7

[NBVC]

I am not sure I understand that statement... can you try again?

[mickreid]

sorry i am not being very clear....

if you look at the sheet i attached you will see that in the first row three of the cells are coloured green, i have a fancy bit of code in the back of the conditional formatting that; as soon as a number is put in the rows below (highlighted yellow) if the number appears in the main rows numbered 1 to 31 then it shades it green, what i would like is a total of these at the end of the row so in the first rows instance as there is three coloured green then it would return three.

regards

Michael

[NBVC]

Since you have duplicates in the yellow zone, you can't use a simple countif or it will double-count....

I have to get going now, so if nobody offers a solution then I will look at either later tonight or tomorrow....

[NBVC]

Actually, just thought of it...

Try:

=COUNT(IF(COUNTIF($C$36:$H$38,C1:L1),1))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

or even:

=SUMPRODUCT(--(COUNTIF($C$36:$H$38,C1:L1)>0))

confirmed with just ENTER and copied down.

[mickreid]

the second one works perfectly, you sir are a prince among men

many thanks

Michael