| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to count cells between dates excluding duplicates
I need this formula to exclude duplicate S/N which are enterd in C33:C2006
for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
Hi!
Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B2006="A5055"),MATCH(C33:C2006,C33:C 2006,0)),MATCH(C33:C2006,C33:C2006,0))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
Hi Biff,
I'm coming up with an "N/A". The "IF" function needs a "value if false"....... Thanks for your help... "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B2006="A5055"),MATCH(C33:C2006,C33:C 2006,0)),MATCH(C33:C2006,C33:C2006,0))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
How would I change the equation below to count the S/N (excluding duplicates)
in column "C". Using the P/N in column "B". Here is the spreadsheet I'm working with. The formula I'm currently trying is listed after the spreadsheet. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") Date P/N S/N 6/26/2006 A5055 1234 6/26/2006 A5055 4321 6/27/2006 A5055 1212 6/28/2006 A5055 1212 6/28/2006 A5055 2121 This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) "Vegs" wrote: Hi Biff, I'm coming up with an "N/A". The "IF" function needs a "value if false"....... Thanks for your help... "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B2006="A5055"),MATCH(C33:C2006,C33:C 2006,0)),MATCH(C33:C2006,C33:C2006,0))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
The "IF" function needs a "value if false".......
The IF function does not need a value_if_false argument. This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) (B33:B2006="5055") Should be: (B33:B2006="A5055") Based on the snippet of data you posted (not knowing what date you have entered in C5)...... See this screencap: http://img119.imageshack.us/img119/2631/sample6vq.jpg Biff "Vegs" wrote in message ... How would I change the equation below to count the S/N (excluding duplicates) in column "C". Using the P/N in column "B". Here is the spreadsheet I'm working with. The formula I'm currently trying is listed after the spreadsheet. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") Date P/N S/N 6/26/2006 A5055 1234 6/26/2006 A5055 4321 6/27/2006 A5055 1212 6/28/2006 A5055 1212 6/28/2006 A5055 2121 This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) "Vegs" wrote: Hi Biff, I'm coming up with an "N/A". The "IF" function needs a "value if false"....... Thanks for your help... "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B2006="A5055"),MATCH(C33:C2006,C33:C 2006,0)),MATCH(C33:C2006,C33:C2006,0))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
It still didn't work.
What if I forget about searching column B and just use the s/n between the dates. Excluding the duplicates of course. I tried this formula but had no success. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--N(FREQUENCY(C33:C2006,C33:C2006)0)) "Biff" wrote: The "IF" function needs a "value if false"....... The IF function does not need a value_if_false argument. This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) (B33:B2006="5055") Should be: (B33:B2006="A5055") Based on the snippet of data you posted (not knowing what date you have entered in C5)...... See this screencap: http://img119.imageshack.us/img119/2631/sample6vq.jpg Biff "Vegs" wrote in message ... How would I change the equation below to count the S/N (excluding duplicates) in column "C". Using the P/N in column "B". Here is the spreadsheet I'm working with. The formula I'm currently trying is listed after the spreadsheet. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") Date P/N S/N 6/26/2006 A5055 1234 6/26/2006 A5055 4321 6/27/2006 A5055 1212 6/28/2006 A5055 1212 6/28/2006 A5055 2121 This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) "Vegs" wrote: Hi Biff, I'm coming up with an "N/A". The "IF" function needs a "value if false"....... Thanks for your help... "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B2006="A5055"),MATCH(C33:C2006,C33:C 2006,0)),MATCH(C33:C2006,C33:C2006,0))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
It still didn't work.
If you looked at the screencap you would have seen that it does in fact work. Would you like for me to look at your file? Is that possible? If it's a huge file I don't need the whole thing, just the sheet with this particular data. Just let me know how to contact you. Biff "Vegs" wrote in message ... It still didn't work. What if I forget about searching column B and just use the s/n between the dates. Excluding the duplicates of course. I tried this formula but had no success. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--N(FREQUENCY(C33:C2006,C33:C2006)0)) "Biff" wrote: The "IF" function needs a "value if false"....... The IF function does not need a value_if_false argument. This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) (B33:B2006="5055") Should be: (B33:B2006="A5055") Based on the snippet of data you posted (not knowing what date you have entered in C5)...... See this screencap: http://img119.imageshack.us/img119/2631/sample6vq.jpg Biff "Vegs" wrote in message ... How would I change the equation below to count the S/N (excluding duplicates) in column "C". Using the P/N in column "B". Here is the spreadsheet I'm working with. The formula I'm currently trying is listed after the spreadsheet. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") Date P/N S/N 6/26/2006 A5055 1234 6/26/2006 A5055 4321 6/27/2006 A5055 1212 6/28/2006 A5055 1212 6/28/2006 A5055 2121 This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) "Vegs" wrote: Hi Biff, I'm coming up with an "N/A". The "IF" function needs a "value if false"....... Thanks for your help... "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B2006="A5055"),MATCH(C33:C2006,C33:C 2006,0)),MATCH(C33:C2006,C33:C2006,0))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
Your absolutely correct Biff, it does work!
Except if there are empty cells in any column, which is the case. What changes to your formula have to be made to make it disregard empty cells? "Biff" wrote: It still didn't work. If you looked at the screencap you would have seen that it does in fact work. Would you like for me to look at your file? Is that possible? If it's a huge file I don't need the whole thing, just the sheet with this particular data. Just let me know how to contact you. Biff "Vegs" wrote in message ... It still didn't work. What if I forget about searching column B and just use the s/n between the dates. Excluding the duplicates of course. I tried this formula but had no success. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--N(FREQUENCY(C33:C2006,C33:C2006)0)) "Biff" wrote: The "IF" function needs a "value if false"....... The IF function does not need a value_if_false argument. This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) (B33:B2006="5055") Should be: (B33:B2006="A5055") Based on the snippet of data you posted (not knowing what date you have entered in C5)...... See this screencap: http://img119.imageshack.us/img119/2631/sample6vq.jpg Biff "Vegs" wrote in message ... How would I change the equation below to count the S/N (excluding duplicates) in column "C". Using the P/N in column "B". Here is the spreadsheet I'm working with. The formula I'm currently trying is listed after the spreadsheet. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") Date P/N S/N 6/26/2006 A5055 1234 6/26/2006 A5055 4321 6/27/2006 A5055 1212 6/28/2006 A5055 1212 6/28/2006 A5055 2121 This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C $5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2 006,C33:C2006,0))0) "Vegs" wrote: Hi Biff, I'm coming up with an "N/A". The "IF" function needs a "value if false"....... Thanks for your help... "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B2006="A5055"),MATCH(C33:C2006,C33:C 2006,0)),MATCH(C33:C2006,C33:C2006,0))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055") |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
Biff's formula can be tweaked as follows...
=SUM(N(FREQUENCY(IF(A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6),I F(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7,IF(B33:B2006 ="A505 5",IF(C33:C2006<"",MATCH(C33:C2006,C33:C2006,0))) )),ROW(A33:A2006)-ROW(A 33)+1)0)) Also, if you download and install the free add-in Morefunc.xll, you can use the following, more efficient, formula... =COUNTDIFF(IF(A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6),IF(A33: A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7,IF(B33:B2006 ="A5055",IF( C33:C2006<"",C33:C2006)))),,FALSE) ....confirmed with CONTROL+SHIFT+ENTER. The add-in can be found at the following link... http://xcell05.free.fr/english/index.html Hope this helps! In article , Vegs wrote: Your absolutely correct Biff, it does work! Except if there are empty cells in any column, which is the case. What changes to your formula have to be made to make it disregard empty cells? "Biff" wrote: It still didn't work. If you looked at the screencap you would have seen that it does in fact work. Would you like for me to look at your file? Is that possible? If it's a huge file I don't need the whole thing, just the sheet with this particular data. Just let me know how to contact you. Biff "Vegs" wrote in message ... It still didn't work. What if I forget about searching column B and just use the s/n between the dates. Excluding the duplicates of course. I tried this formula but had no success. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),-- ($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--N(FREQUENCY(C 33:C2006,C33:C2006)0)) "Biff" wrote: The "IF" function needs a "value if false"....... The IF function does not need a value_if_false argument. This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6 ))*(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5 055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C200 6,C33:C2006,0))0) (B33:B2006="5055") Should be: (B33:B2006="A5055") Based on the snippet of data you posted (not knowing what date you have entered in C5)...... See this screencap: http://img119.imageshack.us/img119/2631/sample6vq.jpg Biff "Vegs" wrote in message ... How would I change the equation below to count the S/N (excluding duplicates) in column "C". Using the P/N in column "B". Here is the spreadsheet I'm working with. The formula I'm currently trying is listed after the spreadsheet. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)) ,--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33: $B$2006="A5055") Date P/N S/N 6/26/2006 A5055 1234 6/26/2006 A5055 4321 6/27/2006 A5055 1212 6/28/2006 A5055 1212 6/28/2006 A5055 2121 This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6 ))*(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5 055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C200 6,C33:C2006,0))0) "Vegs" wrote: Hi Biff, I'm coming up with an "N/A". The "IF" function needs a "value if false"....... Thanks for your help... "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B200 6="A5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33 :C2006,C33:C2006,0 ))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4, 5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7) ,--($B$33:$B$2006="A5055") |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
Domenic,
I get an error located in the last part of the formula...."too few arguments for this function" the "zero" is highlighted.... ........ROW(A33:A2006)-ROW(A33)+1)0)) "Domenic" wrote: Biff's formula can be tweaked as follows... =SUM(N(FREQUENCY(IF(A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6),I F(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7,IF(B33:B2006 ="A505 5",IF(C33:C2006<"",MATCH(C33:C2006,C33:C2006,0))) )),ROW(A33:A2006)-ROW(A 33)+1)0)) Also, if you download and install the free add-in Morefunc.xll, you can use the following, more efficient, formula... =COUNTDIFF(IF(A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6),IF(A33: A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7,IF(B33:B2006 ="A5055",IF( C33:C2006<"",C33:C2006)))),,FALSE) ....confirmed with CONTROL+SHIFT+ENTER. The add-in can be found at the following link... http://xcell05.free.fr/english/index.html Hope this helps! In article , Vegs wrote: Your absolutely correct Biff, it does work! Except if there are empty cells in any column, which is the case. What changes to your formula have to be made to make it disregard empty cells? "Biff" wrote: It still didn't work. If you looked at the screencap you would have seen that it does in fact work. Would you like for me to look at your file? Is that possible? If it's a huge file I don't need the whole thing, just the sheet with this particular data. Just let me know how to contact you. Biff "Vegs" wrote in message ... It still didn't work. What if I forget about searching column B and just use the s/n between the dates. Excluding the duplicates of course. I tried this formula but had no success. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),-- ($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--N(FREQUENCY(C 33:C2006,C33:C2006)0)) "Biff" wrote: The "IF" function needs a "value if false"....... The IF function does not need a value_if_false argument. This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6 ))*(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5 055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C200 6,C33:C2006,0))0) (B33:B2006="5055") Should be: (B33:B2006="A5055") Based on the snippet of data you posted (not knowing what date you have entered in C5)...... See this screencap: http://img119.imageshack.us/img119/2631/sample6vq.jpg Biff "Vegs" wrote in message ... How would I change the equation below to count the S/N (excluding duplicates) in column "C". Using the P/N in column "B". Here is the spreadsheet I'm working with. The formula I'm currently trying is listed after the spreadsheet. =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)) ,--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33: $B$2006="A5055") Date P/N S/N 6/26/2006 A5055 1234 6/26/2006 A5055 4321 6/27/2006 A5055 1212 6/28/2006 A5055 1212 6/28/2006 A5055 2121 This is the formula I'm trying but is not working. =SUM(N(FREQUENCY(IF((A33:A2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6 ))*(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006= "5 055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C200 6,C33:C2006,0))0) "Vegs" wrote: Hi Biff, I'm coming up with an "N/A". The "IF" function needs a "value if false"....... Thanks for your help... "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(N(FREQUENCY(IF((A33:A2006=Date1)*(A33:A2006< Date1+7)*(B33:B200 6="A5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33 :C2006,C33:C2006,0 ))0)) In order to shorten the formula just a little I used a named formula: Date1 refers to: =$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6) Biff "Vegs" wrote in message ... I need this formula to exclude duplicate S/N which are enterd in C33:C2006 for the part "A5055" =SUMPRODUCT(--($A$33:$A$2006=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4, 5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7) ,--($B$33:$B$2006="A5055") |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
I just tested the formula and it seems fine. Did you copy the formula
from the post and paste it into you worksheet? If so, see if hard returns have been added to the formula and remove them. Does this help? In article , Vegs wrote: Domenic, I get an error located in the last part of the formula...."too few arguments for this function" the "zero" is highlighted.... ........ROW(A33:A2006)-ROW(A33)+1)0)) |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to count cells between dates excluding duplicates
Sorry, Domenic...it does work correctly...I copied it again and it was
fine...I couldn't figure out why....the formula's were the same...... Thanks for your help!! Greatly appreciated!!! "Domenic" wrote: I just tested the formula and it seems fine. Did you copy the formula from the post and paste it into you worksheet? If so, see if hard returns have been added to the formula and remove them. Does this help? In article , Vegs wrote: Domenic, I get an error located in the last part of the formula...."too few arguments for this function" the "zero" is highlighted.... ........ROW(A33:A2006)-ROW(A33)+1)0)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use countif to count values excluding blank cells | Excel Worksheet Functions | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
| Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
Linear Mode
