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")

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: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" <[email protected]> wrote in message
news:[email protected]...
>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")

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: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" <[email protected]> wrote in message
> news:[email protected]...
> >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")
>
>
>

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:C2006,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: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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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")
> >
> >
> >

>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:C2006,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" <[email protected]> wrote in message
news:[email protected]...
> 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:C2006,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: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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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")
>> >
>> >
>> >

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:C2006,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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:C2006,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: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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >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")
> >> >
> >> >
> >> >
>
>
>

>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" <[email protected]> wrote in message
news:[email protected]...
> 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:C2006,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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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:C2006,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: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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > >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")
>> >> >
>> >> >
>> >> >
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:C2006,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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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:C2006,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: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" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> > >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")
> >> >> >
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>
>

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 <[email protected]>,
Vegs <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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:C2006,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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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:C2006,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" <[email protected]> wrote in message
> > >> >> > news:[email protected]...
> > >> >> > >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")
> > >> >> >
> > >> >> >
> > >> >> >
> > >>
> > >>
> > >>
> >
> >
> >

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 <[email protected]>,
> Vegs <[email protected]> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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:C2006,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" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> > 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:C2006,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" <[email protected]> wrote in message
> > > >> >> > news:[email protected]...
> > > >> >> > >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")
> > > >> >> >
> > > >> >> >
> > > >> >> >
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>

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 <[email protected]>,
Vegs <[email protected]> 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))

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 <[email protected]>,
> Vegs <[email protected]> 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))
>