Help with Formula
 

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

Help with Formula
 

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

Help with Formula
 

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)))


--
Regards,

Peo Sjoblom


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

Help with Formula
 

Hi Pete,

Thanks thats fantastic it was doing my head in 8-)

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

Help with Formula
 

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

Help with Formula
 

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


On Jul 3, 11:40 pm, Tanya wrote:
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- Hide quoted text -

- Show quoted text -

Help with Formula
 

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

Help with Formula
 

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

Thanks again
Tanya

"Peo Sjoblom" wrote:

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)))


--
Regards,

Peo Sjoblom


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

Help with Formula
 

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

Pete

On Jul 4, 12:10 am, Tanya wrote:
Thankyou both i now have it doing exactly what i need 8-) you guys are
wonderful
i tweaked a couple of things and its working great!

Thanks again
Tanya



"Peo Sjoblom" wrote:
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/C*OUNTIF($AB$16:$AB$30,AB16)))

--
Regards,

Peo Sjoblom

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

- Show quoted text -