Assuming that A2:D7 contains your data, try...

=SUMPRODUCT(--(A2:A7=F2),--(MATCH(C2:C7&"",C2:C7&"",0)=ROW(C2:C7)-ROW(C2)
+1))

....where F2 contains the department of interest, such as 331.

Hope this helps!

In article ,
JR573PUTT
wrote:

The following formula counts the number of non blank cels in row e,
and totals the number of cels that are non blank on the summary page:
{=sumif((dress! a:a = a1)*(dress!e:e <=),1))}

I also want to NOT add any cell that has a duplicate reference number
in column c.


dress sheet:

a b c d e
dept name color units
331 JJ wht 12
331 JJ blk 12
331 JJ blk 12
332 CC blk 12
332 CD blk 12
332 CE blk 12


On the summary sheet for dept 331, the answer should be 2
Because there are 2 unique styles - style JJ in white and style JJ in
black in dept 331. I do not want to count the JJ in black twice, so the
current formula has to NOT count the duplicate