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 color style name units
331 blk 1 12
331 blk 2 12
331 blk 2 12
332 blk 4 12
332 blk 5 12
332 blk 6 12
332 blk 6 12
332 blk 7 12


On the summary sheet for dept 331, the answer should be 2
Because there are 2 unique styles - style 1 and style 2 in dept 331.

I assume I have to add to my existing formula


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

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 doubt that formula would work at all.


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 color style name units
331 blk 1 12
331 blk 2 12
331 blk 2 12
332 blk 4 12
332 blk 5 12
332 blk 6 12
332 blk 6 12
332 blk 7 12


On the summary sheet for dept 331, the answer should be 2
Because there are 2 unique styles - style 1 and style 2 in dept 331.

I assume I have to add to my existing formula


If you download and install the morefunc.xll add-in:

=COUNTDIFF(IF(A2:A9=331,C2:C9,0),FALSE,0)

which you need to confirm with control+shift+enter.

But this forula does not reference the column of units(column d), thus
it will not give me an answer of 2 because it does not look at this
column, it only looks at the column I reference(dept) and column I do
not want to duplicate.........


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

My current array forula does work:
=SUM(IF((DRESS!$A$2:$A$1000=A16)*(DRESS!$E$2:$E$10 00<""),1))


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