I'm still not sure which vaiables are in which columns, but if you put this
in row 2 of the next available column
=IF(A2=$H$1,MATCH(1,INDEX((A$2:A$7=$H$1)*(B$2:B$7& "#"&C$2:C$7=INDEX(B$2:B$7&"#"&C$2:C$7,ROW()-(ROW($C$2)-1))),0),0)+1=ROW())
You can change the column B and column C to whichever columns you are trying
to avoid duplicates. Then copy it down, which will give you a true/false
column.

Assuming H1 holds the department of interest and column F is the true/false
column,
then you can use
=SUMPRODUCT(($A$2:$A$7=H1)*($F$2:$F$7))
to get your count, or
=SUMPRODUCT(($A$2:$A$7=H1)*($F$2:$F$7)*isnumber(E$ 2:E$7))
to count non blank in column E that meet the conditions, or
=SUMPRODUCT(($A$2:$A$7=H1)*($F$2:$F$7)*(E$2:E$7))
to sum column E that meets the conditions (taking only the first instance of
duplicates from the other columns).

If anyone out there knows how to put the first formula inside the second,
I'd love to see (learn) that.


"JR573PUTT" wrote
in message ...

Formula did not work, returned a value 7 times higher than correct
answer, I tried your formula as a regular and array, my original is an
array formula....


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile:
http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513715