Maybe you should explain what you are trying to do? Since you divide
1/countif and if the countif returns 1 then the answer will be 1 regardless
of lot or Y

Maybe this is what you want

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



--
Regards,

Peo Sjoblom


"Tanya" wrote in message
...
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