Let's assume that the data contains the following...

Name Status
X Started
X Started
B Started
B Started
C Stop
C Stop
C Stop
D Stop

If you'd like the formula to return 2, representing the number of times
a combination occurs more than once and where the corresponding name
occurs in Name_validation_list, try the following formula that needs to
be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(Name,Name_vali dation_list,0)),MATCH("
~"&Name&Status,Name&Status&"",0)),ROW(Name)-MIN(ROW(Name))+1)1,1))

Hope this helps!

In article ,
John_J wrote:

I have three named ranges: Name, Status, Name_validation_list. The two first
are about 1000 rows long. I need to get a warning if someone tries to enter
two identical occurances or combinations - He "C" and "Stop" are occuring
more than one time. The warning may perhaps be built from the SUMPRODUCT
returning lager than 1. This is quite easily accomplished with an IF formula.
I want to return the warning in one cell only. Example: $C$1. The criterion
in the "Status" range is fixed: "Stop", but the criterion in the "Name" range
is not. Therefore I have to test the status "Stop" to any of the values in
the named range "Name_validation_list".

A formula testing only one argument is easy and would look like this:
=SUMPRODUCT((Name="C")*(Status="Stop")) Returning "2".
However I need to test against any value: A, B, C, D, ......etc. - as in the
named range Name_validation_list. I believe this could be done through an
array formula inside the SUMPRODUCT formula, but I can't figure it out. The
Name_validation_list is about 50 rows long, so doing one by one would be to
difficult. I also don't want to add another column to assist the calculation.

Name Status
X Started
B Started
C Stop
C Stop
D Stop



Name_validation_list
A
B
C
D
E
F