Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default 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.


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513715

  #4   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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



  #6   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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

  #7   Report Post  
Posted to microsoft.public.excel.misc
Bob Tarburton
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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" wrote
in message ...

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



  #8   Report Post  
Posted to microsoft.public.excel.misc
Bob Tarburton
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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" wrote
in message ...

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



  #9   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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

  #10   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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:C 7)-ROW(C2)+1))

will return 2 using your posted example data

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"JR573PUTT" wrote
in message ...

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




  #11   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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

  #12   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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:C 7)-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" wrote
in message ...

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


  #13   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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

  #14   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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

  #15   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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 ,
JR573PUTT
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



  #16   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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

  #17   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513715

  #18   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default Array Formula Sum If With Duplicate

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 ,
JR573PUTT
wrote:

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

  #19   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Array Formula Sum If With Duplicate


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


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513715

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Formula Not Working with Range with Formulas [email protected] Excel Discussion (Misc queries) 4 February 1st 06 03:01 PM
Array Formula to Pick Average SludgeQuake Excel Discussion (Misc queries) 3 January 11th 06 01:59 AM
problem with Array Formula OrdOff Excel Worksheet Functions 2 June 30th 05 04:57 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Array Formula Doug at HAL Excel Worksheet Functions 3 December 21st 04 11:27 AM


All times are GMT +1. The time now is 10:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"