Array Formula With Multiple Conditions

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 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.........

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