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
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513399

Array Formula With Multiple Conditions
 

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.

Array Formula With Multiple Conditions
 

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

Array Formula With Multiple Conditions
 

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