Sorry me again, the only problem i have is that when i delete the word lot
out of B16, A16 still contains 1 when it should be 0? and the same with when
i delete Y out of C16 again A16 should be 0 it needs to meet both those
criteria to = 1, which is what i thought the formula was doing?

Thanks again
tanya

"Pete_UK" wrote:

I think you mean something like this:

=IF(AB16="",0,IF(AND(COUNTIF($AB$16:$AB$30,AB16)=1 ,B16="lot",C16="Y"),
1,1/COUNTIF($AB$16:$AB$30,AB16)))

All one formula - be wary of spurious linebreaks.

Hope this helps.

Pete


On Jul 3, 11:14 pm, Tanya wrote:
Hi all,

I am getting so confused with this formula that my head is aching can anyone
help?
=IF((AB16="",0,AND(COUNTIF($AB$16:$AB$30,AB16)=1,B 16='lot',
C16='Y')),COUNTIF($AB$16:$AB$30,AB16),1/COUNTIF($AB$16:$AB$30,AB16)))

I am guessing that i dont have the syntax correct or the brackets in the
correct place, please let me know if you need further explanation although im
just as confused.

Thanks Tanya