Help with Formula

[Guest]

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,B16='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

 

[Pete_UK]

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

 

[Peo Sjoblom]

The first part looks like it should be

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

now the question is, what do you want if the IF(AND part is TRUE

If the countif part is 1, B16 equals lot and C16 is Y, what do you want as a
result? If you want the result of the countif you might as well put 1 there
since that is the condition, so maybe


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

 

[Guest]

Hi Pete,

Thanks thats fantastic it was doing my head in

Tanya

 

[Guest]

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]

Okay, Tanya, I think you need this slight variation to achieve that:

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

Hope this helps.

Pete

 

[Peo Sjoblom]

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,AB16)=1,1,1/COUNTIF($AB$16:$AB$30,AB16)))

 

[Guest]

Thankyou both i now have it doing exactly what i need you guys are
wonderful
i tweaked a couple of things and its working great!

Thanks again
Tanya

 

[Pete_UK]

Good to hear that you got it to work as you wanted - thanks for
feeding back.

Pete