Array Formula Sum If With 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.

Assuming that Column A contains the department, try the following
formula instead...

=SUMPRODUCT(--(A2:A7=G2),--(MATCH(C2:C7&"#"&D2:D7,C2:C7&"#"&D2:D7,0)=ROW(
C2:C7)-ROW(C2)+1),E2:E7)

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

Hope this helps!

In article <[email protected]>,
JR573PUTT <[email protected]>
wrote:

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

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

I think the formula recommendation here is adding the column in reference vs omitting duplicates..........

I just responded with a "column added" 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

"JR573PUTT" <[email protected]> wrote
in message news:[email protected]...
>
> I think the formula recommendation here is adding the column in
> reference vs omitting duplicates..........
>
>
> --
> JR573PUTT
> ------------------------------------------------------------------------
> JR573PUTT's Profile:
> http://www.excelforum.com/member.php...o&userid=31587
> View this thread: http://www.excelforum.com/showthread...hreadid=513715
>

I'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$7&"#"&C$2:C$7=INDEX(B$2:B$7&"#"&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.


"JR573PUTT" <[email protected]> wrote
in message news:[email protected]...
>
> 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:
> http://www.excelforum.com/member.php...o&userid=31587
> View this thread: http://www.excelforum.com/showthread...hreadid=513715
>