Formula in table changes when new row is added

Hey all,
For the life of me I can't figure out why the formula in the last row updates and changes when I add a new row (making the incorrect cell now in the second to last row). Excel even recognizes that it's wrong and gives a little warning ("This cell is inconsistent with the column formula"). The formula in the new column is just fine, but will also become wrong if a new row is added below it.

Attached is the table, the formulas are in column B.

=IF(SUMPRODUCT(($D$2:$D2=D2)*($A$2:$A2=A2))>1,0,1)
=IF(SUMPRODUCT(($D$2:$D3=D3)*($A$2:$A3=A3))>1,0,1)
and so on down the column.

However, when a new row is added the second to last row would show
=IF(SUMPRODUCT(($D$2:$D15=D14)*($A$2:$A15=A14))>1,0,1)
instead of the 14s (no 15s) it should.

I would like to be able to keep column B hidden and not have to make sure that it's not messing up all the time

It looks like Excel has decided that you want to have the whole column range in the formula instead of the one you have specified - another case of it trying to be helpful but failing.
I've tried a number of things to fix it - this is the one which seems to work (in other words, if I enter a new row 16, the formula for row 15 stays the same as it was before):

Formula:

Please Login or Register  to view this content.

Does that do what you need?

You're welcome, glad to be of help and thanks for marking the thread as Solved.

Edit: and thanks for the rep