Array Formula Sum If With Duplicate

[JR573PUTT]

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


On 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

[Domenic]

Assuming that A2:D7 contains your data, try...

=SUMPRODUCT(--(A2:A7=F2),--(MATCH(C2:C7&"",C2:C7&"",0)=ROW(C2:C7)-ROW(C2)
+1))

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

Hope this helps!

In article <[email protected]>,
JR573PUTT <[email protected]>
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 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 12
>
>
> On 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]

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.

[Domenic]

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.

[JR573PUTT]

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]

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

[Bob Tarburton]

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
>

[Bob Tarburton]

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
>

[JR573PUTT]

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!

[Peo Sjoblom]

Use Domenic's formula but take off the SUM part

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

will return 2 using your posted example data

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




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

[JR573PUTT]

The formula does not reference colume E which is the main column, while your formula will return 2, it is because it is just adding column C,D.

I could have the same style color with 0 pairs in addition to the 2 in the example and your formula would return a value of 3, but the correct answer is 2.

[Peo Sjoblom]

What part of column E do you need given that you want to count the number of
unique entries in C and D, if you don't want to count E if E is blank you
can use

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

otherwise post back with some data showing what you want as opposed to what
you get using Domenic's formula

however using the sample you posted and Domenic's formula it returns 2 which
you said you wanted



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"JR573PUTT" <[email protected]> wrote
in message news:[email protected]...
>
> The formula does not reference colume E which is the main column, while
> your formula will return 2, it is because it is just adding column
> C,D.
>
> I could have the same style color with 0 pairs in addition to the 2 in
> the example and your formula would return a value of 3, but the correct
> answer is 2.
>
>
> --
> JR573PUTT
> ------------------------------------------------------------------------
> JR573PUTT's Profile:
> http://www.excelforum.com/member.php...o&userid=31587
> View this thread: http://www.excelforum.com/showthread...hreadid=513715
>

[JR573PUTT]

Detail sheet is as follows:

COLUMN A: DEPT, EXAMPLE 331, 332
COLUMN B: STYLE NAME, EXAMPLE: THALIA, JANE
COLUMN C: COLOR, EXAMPLE: BLACK, WHITE
COLUMN D: QTY , EXAMPLE: 12, 24


SUB STYLE NAME COLOR QTY
331 RAVEN BLACK
331 RAVEN WHITE
331 THALIA WHITE
331 THALIA PINK
331 JANE BLACK
331 JANE BLACK 12
331 JANE BLACK 24
331 JANE GOLD 12
331 JANE SILVER 12
331 JANE SILVER 24
331 JANE WHITE
331 JANE WHITE 24
331 JANE RED 12
331 RACY BLACK
331 RACY BONE
331 JANIE BLACK 12
331 JANIE BLACK 24
331 JANIE BRONZE
331 JANIE ORANGE
331 JANIS BLACK
331 JANIS WHITE
331 JANIS RED
331 JANIS BLACK 12
331 JANIS WHITE 12
331 JANIS RED 12
331 VIVIAN BLACK
331 VIVIAN WHITE
331 VIVIAN-P BLACK
331 VIVIAN-L BLACK
331 VIVIAN-L RED
331 VIVIAN-L NAVY
331 VIVIAN-L WHITE
331 VIOLET BLACK
331 VIOLET BROWN
331 ELSIE F BEIGE 12
331 KITTY SILVER
331 KITTY BLACK
331 KITTY BRONZE
331 PIXIE GREEN 12
331 PIXIE ORANGE 12
331 PIXIE WHITE 12
331 CELESTE BLACK
331 CELESTE BONE
331 CELESTE BROWN
331 RACHELD BROWN 12
331 RACHELD GREEN 12
331 RACHELD BONE 12
331 MEGAN BLACK
331 MEGAN WHITE
331 SHEENA BLACK
331 SHEENA GOLD
331 SHEENA SILVER
331 SHEENA WHITE
331 SHEENA BLACK 12
331 SHEENA GOLD 12
331 SHEENA SILVER 12
331 SHEENA WHITE 12
331 LAVAL BONE 12
331 LAVAL BROWN 12
331 LAVAL GREEN 12

332 SHELLY BLACK
332 SHELLY NATURAL
332 SHELLY BLACK
332 SHELLY NATURAL
332 SHELLY BLACK 24
332 SHELLY NATURAL 24
332 SHELLY BLACK
332 SHELLY GREEN
332 SHELLY LAVENDER
332 SHELLY YELLOW
332 SHELLY GREEN 12
332 SHELLY YELLOW 12
332 SHELLY LAVENDER
332 SHELLY BRONZE 12
332 SHELLY ORANGE 12
332 FIONA BLACK
332 FIONA BRONZE
332 PATRICIA BLACK 12
332 PATRICIA WHITE 12
332 PATRICIA TURQ 12
332 PATRICIA PURPLE 12

Summary sheet is as follows:


In the ACT u column I have a sumproduct formula that adds the pairs for each subdepartment and works fine.

The formla I am struggling with is for the SKUs column, there are 23 SKUS or unique styles in column D, example, Jane in black, RachelD in green, etc.....The Jane in black is listed twice because of different purchase orders of same product, I only want to count the Jane black once if there is a value in the qty column. This is called a SKU count, this is important because a display only holds X amount.................

I need a formula to count how many unique entries are in column D.

The answer I am looking for 331 is 23 because there are 23 unique skus in column d with qty.


DEPT Name PLAN u ACT u U -/+ SKUs
331 CLOSED 420 324 -96 #N/A
332 OPEN 552 768 216 50

[Domenic]

I'm with Peo. I'm really not sure what it is you're looking for. It
would help if you could post a 'representative' sample of data, along
with your expected results...

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

> The formula does not reference colume E which is the main column, while
> your formula will return 2, it is because it is just adding column
> C,D.
>
> I could have the same style color with 0 pairs in addition to the 2 in
> the example and your formula would return a value of 3, but the correct
> answer is 2.

[Domenic]

Okay, I think I got it...

=COUNT(1/FREQUENCY(IF(A2:A83=F2,IF(D2:D83<>"",MATCH(B2:B83&"#"&C2:C83,B2:
B83&"#"&C2:C83,0))),ROW(A2:A83)-ROW(A2)+1))

....where F2 contains the department of interest. Note that the formula
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

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

> Detail sheet is as follows:
>
> COLUMN A: DEPT, EXAMPLE 331, 332
> COLUMN B: STYLE NAME, EXAMPLE: THALIA, JANE
> COLUMN C: COLOR, EXAMPLE: BLACK, WHITE
> COLUMN D: QTY , EXAMPLE: 12, 24
>
>
> SUB STYLE NAME COLOR QTY
> 331 RAVEN BLACK
> 331 RAVEN WHITE
> 331 THALIA WHITE
> 331 THALIA PINK
> 331 JANE BLACK
> 331 JANE BLACK 12
> 331 JANE BLACK 24
> 331 JANE GOLD 12
> 331 JANE SILVER 12
> 331 JANE SILVER 24
> 331 JANE WHITE
> 331 JANE WHITE 24
> 331 JANE RED 12
> 331 RACY BLACK
> 331 RACY BONE
> 331 JANIE BLACK 12
> 331 JANIE BLACK 24
> 331 JANIE BRONZE
> 331 JANIE ORANGE
> 331 JANIS BLACK
> 331 JANIS WHITE
> 331 JANIS RED
> 331 JANIS BLACK 12
> 331 JANIS WHITE 12
> 331 JANIS RED 12
> 331 VIVIAN BLACK
> 331 VIVIAN WHITE
> 331 VIVIAN-P BLACK
> 331 VIVIAN-L BLACK
> 331 VIVIAN-L RED
> 331 VIVIAN-L NAVY
> 331 VIVIAN-L WHITE
> 331 VIOLET BLACK
> 331 VIOLET BROWN
> 331 ELSIE F BEIGE 12
> 331 KITTY SILVER
> 331 KITTY BLACK
> 331 KITTY BRONZE
> 331 PIXIE GREEN 12
> 331 PIXIE ORANGE 12
> 331 PIXIE WHITE 12
> 331 CELESTE BLACK
> 331 CELESTE BONE
> 331 CELESTE BROWN
> 331 RACHELD BROWN 12
> 331 RACHELD GREEN 12
> 331 RACHELD BONE 12
> 331 MEGAN BLACK
> 331 MEGAN WHITE
> 331 SHEENA BLACK
> 331 SHEENA GOLD
> 331 SHEENA SILVER
> 331 SHEENA WHITE
> 331 SHEENA BLACK 12
> 331 SHEENA GOLD 12
> 331 SHEENA SILVER 12
> 331 SHEENA WHITE 12
> 331 LAVAL BONE 12
> 331 LAVAL BROWN 12
> 331 LAVAL GREEN 12
>
> 332 SHELLY BLACK
> 332 SHELLY NATURAL
> 332 SHELLY BLACK
> 332 SHELLY NATURAL
> 332 SHELLY BLACK 24
> 332 SHELLY NATURAL 24
> 332 SHELLY BLACK
> 332 SHELLY GREEN
> 332 SHELLY LAVENDER
> 332 SHELLY YELLOW
> 332 SHELLY GREEN 12
> 332 SHELLY YELLOW 12
> 332 SHELLY LAVENDER
> 332 SHELLY BRONZE 12
> 332 SHELLY ORANGE 12
> 332 FIONA BLACK
> 332 FIONA BRONZE
> 332 PATRICIA BLACK 12
> 332 PATRICIA WHITE 12
> 332 PATRICIA TURQ 12
> 332 PATRICIA PURPLE 12
>
> Summary sheet is as follows:
>
>
> In the ACT u column I have a sumproduct formula that adds the pairs for
> each subdepartment and works fine.
>
> The formla I am struggling with is for the SKUs column, there are 23
> SKUS or unique styles in column D, example, Jane in black, RachelD in
> green, etc.....The Jane in black is listed twice because of different
> purchase orders of same product, I only want to count the Jane black
> once if there is a value in the qty column. This is called a SKU
> count, this is important because a display only holds X
> amount.................
>
> I need a formula to count how many unique entries are in column D.
>
> The answer I am looking for 331 is 23 because there are 23 unique skus
> in column d with qty.
>
>
> DEPT Name PLAN u ACT u U -/+ SKUs
> 331 CLOSED 420 324 -96 #N/A
> 332 OPEN 552 768 216 50

[JR573PUTT]

column A col B col C col D
Department Name color quantity
331 jane black 12
331 jane black 12
331 jane red 12
331 jane red
331 jane black
331 jane green 12
332 bill black 12
332 bill red 12
332 bill red
332 sue purple 12

The formula should return a value of 3 for department 331 because the following are unique and have quantity:

Jane black
Jane red
Jane green

The formula has to look at column b and c to make sure they are not duplicated(don't count jane black twice because it has a quantity twice, it is one style) and the formula has to reference column D because the quantity resides here, and the formula has to reference A because there are more than one department.......

Hope you understand what formula I am looking for.

[JR573PUTT]

The count frequency is not working, anyone have any ideas?

[Domenic]

Using the following sample data from your previous post...

column A col B col C col D
Department Name color quantity
331 jane black 12
331 jane black 12
331 jane red 12
331 jane red
331 jane black
331 jane green 12
332 bill black 12
332 bill red 12
332 bill red
332 sue purple 12

....if F2 contains the department of interest, let's say 331, the
following formula...

=COUNT(1/FREQUENCY(IF(A2:A11=F2,IF(D2:D11<>"",MATCH(B2:B11&
"#"&C2:C11,B2:B11&"#"&C2:C11,0))),ROW(A2:A11)-ROW(A2)+1))

....returns 3. Note that the formula needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER. What's the result that you get?

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

> The count frequency is not working, anyone have any ideas?

[JR573PUTT]

Thank you, the formula works! I must have done something wrong earlier, thanks again, this was a tough one.