Array Formula Sum If With Duplicate

J

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
 
D

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!
 
J

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

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!
 
J

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

JR573PUTT

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

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

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
 
J

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!
 
P

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
 
J

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

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
 
D

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

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
 
D

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!
 
J

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

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?
 
J

JR573PUTT

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Array Formula With Multiple Conditions 3
array formula with duplicates 0
Array formula with 2 conditions... 0
countif counta with multiple lookup criteria 1
Countif With Critiera 4
Any body out there smart enough??? 9
Sumproducts, Counta Lookup Ref Formulas 7
Lookups and References in relations to countA and sum products.... 5

Top