Hi All,
I have data that is setup with Owners in Column A and Date Purchased in
Column B.
What formula would I use to calculate the number of items that were
purchased by Customer 1 between 1 and 2 weeks ago?
THanks
Andi
Hi All,
I have data that is setup with Owners in Column A and Date Purchased in
Column B.
What formula would I use to calculate the number of items that were
purchased by Customer 1 between 1 and 2 weeks ago?
THanks
Andi
Hi Andi
One way
=SUMPRODUCT(--($A$1:$A$100=customer_name),--($B$1:$B$100>=DATE(2005,7,23)),--($B$1:$B$100<=DATE(2005,7,29))
Change ranges and date to suit.
--
Regards
Roger Govier
"Andibevan" <[email protected]> wrote in message
news:O%[email protected]...
> Hi All,
>
> I have data that is setup with Owners in Column A and Date Purchased in
> Column B.
>
> What formula would I use to calculate the number of items that were
> purchased by Customer 1 between 1 and 2 weeks ago?
>
> THanks
>
> Andi
>
>
Hi Andi
One way
=SUMPRODUCT(--($A$1:$A$100=customer_name),--($B$1:$B$100>=DATE(2005,7,23)),--($B$1:$B$100<=DATE(2005,7,29))
Change ranges and date to suit.
--
Regards
Roger Govier
"Andibevan" <[email protected]> wrote in message
news:O%[email protected]...
> Hi All,
>
> I have data that is setup with Owners in Column A and Date Purchased in
> Column B.
>
> What formula would I use to calculate the number of items that were
> purchased by Customer 1 between 1 and 2 weeks ago?
>
> THanks
>
> Andi
>
>
=SUMPRODUCT(--($A$2:$A$100="Customer
1"),--($B$2:$B$100>TODAY()-14),--($B$2:$B$100<=TODAY()-7)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Andibevan" <[email protected]> wrote in message
news:O%[email protected]...
> Hi All,
>
> I have data that is setup with Owners in Column A and Date Purchased in
> Column B.
>
> What formula would I use to calculate the number of items that were
> purchased by Customer 1 between 1 and 2 weeks ago?
>
> THanks
>
> Andi
>
>
=SUMPRODUCT(--($A$2:$A$100="Customer
1"),--($B$2:$B$100>TODAY()-14),--($B$2:$B$100<=TODAY()-7)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Andibevan" <[email protected]> wrote in message
news:O%[email protected]...
> Hi All,
>
> I have data that is setup with Owners in Column A and Date Purchased in
> Column B.
>
> What formula would I use to calculate the number of items that were
> purchased by Customer 1 between 1 and 2 weeks ago?
>
> THanks
>
> Andi
>
>
Great - Thanks Guys
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--($A$2:$A$100="Customer
> 1"),--($B$2:$B$100>TODAY()-14),--($B$2:$B$100<=TODAY()-7)
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Andibevan" <[email protected]> wrote in message
> news:O%[email protected]...
> > Hi All,
> >
> > I have data that is setup with Owners in Column A and Date Purchased in
> > Column B.
> >
> > What formula would I use to calculate the number of items that were
> > purchased by Customer 1 between 1 and 2 weeks ago?
> >
> > THanks
> >
> > Andi
> >
> >
>
>
Great - Thanks Guys
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--($A$2:$A$100="Customer
> 1"),--($B$2:$B$100>TODAY()-14),--($B$2:$B$100<=TODAY()-7)
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Andibevan" <[email protected]> wrote in message
> news:O%[email protected]...
> > Hi All,
> >
> > I have data that is setup with Owners in Column A and Date Purchased in
> > Column B.
> >
> > What formula would I use to calculate the number of items that were
> > purchased by Customer 1 between 1 and 2 weeks ago?
> >
> > THanks
> >
> > Andi
> >
> >
>
>
How would I get this to work if Column B contains the date and time not just
the date - i.e. 6/7/2005 11:52:59
Hadn't spotted that when I originally posted.
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--($A$2:$A$100="Customer
> 1"),--($B$2:$B$100>TODAY()-14),--($B$2:$B$100<=TODAY()-7)
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Andibevan" <[email protected]> wrote in message
> news:O%[email protected]...
> > Hi All,
> >
> > I have data that is setup with Owners in Column A and Date Purchased in
> > Column B.
> >
> > What formula would I use to calculate the number of items that were
> > purchased by Customer 1 between 1 and 2 weeks ago?
> >
> > THanks
> >
> > Andi
> >
> >
>
>
How would I get this to work if Column B contains the date and time not just
the date - i.e. 6/7/2005 11:52:59
Hadn't spotted that when I originally posted.
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--($A$2:$A$100="Customer
> 1"),--($B$2:$B$100>TODAY()-14),--($B$2:$B$100<=TODAY()-7)
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Andibevan" <[email protected]> wrote in message
> news:O%[email protected]...
> > Hi All,
> >
> > I have data that is setup with Owners in Column A and Date Purchased in
> > Column B.
> >
> > What formula would I use to calculate the number of items that were
> > purchased by Customer 1 between 1 and 2 weeks ago?
> >
> > THanks
> >
> > Andi
> >
> >
>
>
It still works, just starts at the beginning of the day.
--
HTH
Bob Phillips
"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> How would I get this to work if Column B contains the date and time not
just
> the date - i.e. 6/7/2005 11:52:59
>
> Hadn't spotted that when I originally posted.
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--($A$2:$A$100="Customer
> > 1"),--($B$2:$B$100>TODAY()-14),--($B$2:$B$100<=TODAY()-7)
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Andibevan" <[email protected]> wrote in message
> > news:O%[email protected]...
> > > Hi All,
> > >
> > > I have data that is setup with Owners in Column A and Date Purchased
in
> > > Column B.
> > >
> > > What formula would I use to calculate the number of items that were
> > > purchased by Customer 1 between 1 and 2 weeks ago?
> > >
> > > THanks
> > >
> > > Andi
> > >
> > >
> >
> >
>
>
It still works, just starts at the beginning of the day.
--
HTH
Bob Phillips
"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> How would I get this to work if Column B contains the date and time not
just
> the date - i.e. 6/7/2005 11:52:59
>
> Hadn't spotted that when I originally posted.
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--($A$2:$A$100="Customer
> > 1"),--($B$2:$B$100>TODAY()-14),--($B$2:$B$100<=TODAY()-7)
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Andibevan" <[email protected]> wrote in message
> > news:O%[email protected]...
> > > Hi All,
> > >
> > > I have data that is setup with Owners in Column A and Date Purchased
in
> > > Column B.
> > >
> > > What formula would I use to calculate the number of items that were
> > > purchased by Customer 1 between 1 and 2 weeks ago?
> > >
> > > THanks
> > >
> > > Andi
> > >
> > >
> >
> >
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks