software

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 lt;gt;=),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 12On 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--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513715Assuming that A27 contains your data, try...

=SUMPRODUCT(--(A2:A7=F2),--(MATCH(C2:C7amp;quot;quot;,C2:C7amp;quot;quot;,0)=ROW(C2:C7)-ROW(C2)
1))

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

Hope this helps!

In article gt;,
JR573PUTT gt;
wrote:

gt; The following formula counts the number of non blank cels in row e,
gt; and totals the number of cels that are non blank on the summary page:
gt; {=sumif((dress! a:a = a1)*(dress!e:e lt;gt;=),1))}
gt;
gt; I also want to NOT add any cell that has a duplicate reference number
gt; in column c.
gt;
gt;
gt; dress sheet:
gt;
gt; a b c d e
gt; dept name color units
gt; 331 JJ wht 12
gt; 331 JJ blk 12
gt; 331 JJ blk 12
gt; 332 CC blk 12
gt; 332 CD blk 12
gt; 332 CE blk 12
gt;
gt;
gt; On the summary sheet for dept 331, the answer should be 2
gt; Because there are 2 unique styles - style JJ in white and style JJ in
gt; black in dept 331. I do not want to count the JJ in black twice, so the
gt; current formula has to NOT count the duplicate


Actually the date to reference is a:e, and column e is the column I want
to count, based on whether or not column C and D not duplicated, if they
are duplicated, count only once.

Column C is the name of the merchandise, column D is the color of the
merchandise, column E is the total units, and what I am doing is
counting the number of colors that have pairs, what we call SKU count.--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513715Assuming that Column A contains the department, try the following
formula instead...

=SUMPRODUCT(--(A2:A7=G2),--(MATCH(C2:C7amp;quot;#quot;amp;D27,C2:C7amp;quot;#quot;amp;D27,0)=ROW(
C2:C7)-ROW(C2) 1),E2:E7)

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

Hope this helps!

In article gt;,
JR573PUTT gt;
wrote:

gt; Actually the date to reference is a:e, and column e is the column I want
gt; to count, based on whether or not column C and D not duplicated, if they
gt; are duplicated, count only once.
gt;
gt; Column C is the name of the merchandise, column D is the color of the
gt; merchandise, column E is the total units, and what I am doing is
gt; counting the number of colors that have pairs, what we call SKU count.


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: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513715
I think the formula recommendation here is adding the column in
reference vs omitting duplicates..........--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513715I'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$7amp; quot;#quot;amp;C$2:C$7=INDEX(B$2:B$7amp;quot;#quot;amp;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.quot;JR573PUTTquot; gt; wrote
in message ...
gt;
gt; Formula did not work, returned a value 7 times higher than correct
gt; answer, I tried your formula as a regular and array, my original is an
gt; array formula....
gt;
gt;
gt; --
gt; JR573PUTT
gt; ------------------------------------------------------------------------
gt; JR573PUTT's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31587
gt; View this thread: www.excelforum.com/showthread...hreadid=513715
gt;
I just responded with a quot;column addedquot; formula.
I'm sure you could use a much easier column added, and slightly more complex
SUMPRODUCT formula than what I offered. However, I was hoping someone could
convert my example into a one cell formula.
Good luck
Bob

quot;JR573PUTTquot; gt; wrote
in message ...
gt;
gt; I think the formula recommendation here is adding the column in
gt; reference vs omitting duplicates..........
gt;
gt;
gt; --
gt; JR573PUTT
gt; ------------------------------------------------------------------------
gt; JR573PUTT's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31587
gt; View this thread: www.excelforum.com/showthread...hreadid=513715
gt;

Thanks, there has to be a way to say if column d and column e repeat,
count column f only once, seems simple, probably is a simple formula,
that is why it is so difficult!--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513715Use Domenic's formula but take off the SUM part

=SUMPRODUCT(--(A2:A7=G2),--(MATCH(C2:C7amp;quot;#quot;amp;D27,C2:C7amp;quot;#quot;amp;D27,0)=ROW(C2:C 7)-ROW(C2) 1))

will return 2 using your posted example data

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

quot;JR573PUTTquot; gt; wrote
in message ...
gt;
gt; Thanks, there has to be a way to say if column d and column e repeat,
gt; count column f only once, seems simple, probably is a simple formula,
gt; that is why it is so difficult!
gt;
gt;
gt; --
gt; JR573PUTT
gt; ------------------------------------------------------------------------
gt; JR573PUTT's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31587
gt; View this thread: www.excelforum.com/showthread...hreadid=513715
gt;