Please help, IF function in an IF function, looking for duplicates

[8thwondurrrr]

First of all here is my function;
=IF(A7<>"",IF(COUNTIF($A$7:$A$33,A7)>1,"0.00",$D$4),"")

This formula pulls adds a fee (cell D4) when you sign in, and SHOULD allow a sign out and not add a duplicate membership fee.

This is meant for column B, is it blank when the parallel cell in column A is blank, however when a name is entered into column A (Member sign in) Column B pulls the Daily membership fee. This Part works!

The Next part of the function says is there is a duplicate in Column A (member Sign out) Column B is now 0 instead of the daily fee. THis also works! Except it makes BOTH entries sign in and sign out fees 0 INSTEAD OF just the SIGN OUT fee being 0.

How can I edit this formula to ONLY change the sign out fee 0 so we DO NOT add 2 Fees and inflate our daily earnings??

THANK YOU IN ADVANCE!

 

[Special-K99]

1. Remove the quotes round the 0.00

2. Replace the formula with either

=IF(A7<>"",IF(COUNTIF($A$7:$A7,A7)>1,0,$D$4),"")
or
=IF(A7<>"",IF(COUNTIF($A$7:$A7,A7)>1,"",$D$4),"")

depending if you want 0 or blank to show on sign out fees

Copy the formula down the column

 

[8thwondurrrr]

That doesn't do it. Both the sign in and sin out both go to zero or blank when the duplicate entry is made

 

[8thwondurrrr]

And what is the purpose of changing the table to "$A$7:$A7" that just has the search limited to one cell and not a range of sign ins.

 

[Special-K99]

And what is the purpose of changing the table to "$A$7:$A7" that just has the search limited to one cell and not a range of sign ins.

Not when the formula is copied to other rows below A7.
The $ anchors the initial cell so it's checking A$7:A7 when copied below A$7:A8, A$7:A9, A$7:A10
It's checking if the result already appears in the list.

 

[Special-K99]

That doesn't do it. Both the sign in and sin out both go to zero or blank when the duplicate entry is made

It darn well does work, I tested it.

In a blank sheet enter a value in D4

Put the formula in B7 and copy down the column.

If you put a value in A7 it copies the value from D4.
If you put the SAME VALUE anywhere in column A further down you get a zero/blank depending which formula you used.

That's what you asked for.

Show me how it doesn't work.

 

[mikerickson]

Try this. Put this formula in row 8
=IF(A8<>"",IF(COUNTIF($A$7:$A7,A8)>0, 0.00,$D$4),"")
and drag down

If the value is column A has a duplicate above it, the formula returns 0, if not it returns $D$4.

Note that this assumes your list of numbers starts at A7

 

[8thwondurrrr]

Hey thank you sir. I was wrong it did work. I think the problem was that I entered the formula when I already a list of values in column A. So it didn't recalculate the existing fees. Thank you guys!