Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Array Formula to Pick Average | Excel Discussion (Misc queries) | |||
problem with Array Formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Array Formula | Excel Worksheet Functions |