+ Reply to Thread
Results 1 to 6 of 6

SUMPRODUCT Question....

Hybrid View

Guest SUMPRODUCT Question.... 12-08-2005, 06:50 PM
Guest Re: SUMPRODUCT Question.... 12-08-2005, 06:55 PM
Guest Re: SUMPRODUCT Question.... 12-08-2005, 07:10 PM
Guest Re: SUMPRODUCT Question.... 12-08-2005, 08:45 PM
Guest Re: SUMPRODUCT Question.... 12-08-2005, 09:00 PM
Guest Re: SUMPRODUCT Question.... 12-08-2005, 09:10 PM
  1. 12-08-2005, 06:50 PM #1
    Jeremy Ellison
    Guest

    SUMPRODUCT Question....

    I have a column (c) with dates...I have column (ba) with text data, sometimes
    RIEP sometimes Charged with (some type of crime). I want to have excel add
    up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 -
    3/31/2006). I am able to do this with the following formula. I then want
    excel to add up the occurances of Charged. I have tried to insert
    "Charged*" ,but this does not work. Is there a better formula for this type
    of action?? I have to havce a variable because I want to be able to have
    data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with......
    what ever....

    =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000>=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    Reply With QuoteRegister To Reply
  2. 12-08-2005, 06:55 PM #2
    Dave Peterson
    Guest

    Re: SUMPRODUCT Question....

    try:

    =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    --(CaseData!$C$2:$C$1000>=--"2006-01-01"),
    --(CaseData!$C$2:$C$1000<=--"2006-03-31"))

    (the asterisks have been replaced with a comma (use your list separator).)

    Personally, I'd be more explicit with the dates:

    =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    --(CaseData!$C$2:$C$1000>=date(2006,1,1)),
    --(CaseData!$C$2:$C$1000<=date(2006,03,31))

    I don't trust excel to get the ymd correct.



    Jeremy Ellison wrote:
    >
    > I have a column (c) with dates...I have column (ba) with text data, sometimes
    > RIEP sometimes Charged with (some type of crime). I want to have excel add
    > up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 -
    > 3/31/2006). I am able to do this with the following formula. I then want
    > excel to add up the occurances of Charged. I have tried to insert
    > "Charged*" ,but this does not work. Is there a better formula for this type
    > of action?? I have to havce a variable because I want to be able to have
    > data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with......
    > what ever....
    >
    > =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000>=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31"))

    --

    Dave Peterson
    Reply With QuoteRegister To Reply
  3. 12-08-2005, 07:10 PM #3
    Jeremy Ellison
    Guest

    Re: SUMPRODUCT Question....

    That worked, but I am still trying to find out how to put a wildcard into the
    formlua to look at anything beginning with charged.... charged VOCSL1 or
    charged VOCSL3 or someother charged.....

    "Dave Peterson" wrote:

    > try:
    >
    > =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    > --(CaseData!$C$2:$C$1000>=--"2006-01-01"),
    > --(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    >
    > (the asterisks have been replaced with a comma (use your list separator).)
    >
    > Personally, I'd be more explicit with the dates:
    >
    > =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    > --(CaseData!$C$2:$C$1000>=date(2006,1,1)),
    > --(CaseData!$C$2:$C$1000<=date(2006,03,31))
    >
    > I don't trust excel to get the ymd correct.
    >
    >
    >
    > Jeremy Ellison wrote:
    > >
    > > I have a column (c) with dates...I have column (ba) with text data, sometimes
    > > RIEP sometimes Charged with (some type of crime). I want to have excel add
    > > up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 -
    > > 3/31/2006). I am able to do this with the following formula. I then want
    > > excel to add up the occurances of Charged. I have tried to insert
    > > "Charged*" ,but this does not work. Is there a better formula for this type
    > > of action?? I have to havce a variable because I want to be able to have
    > > data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with......
    > > what ever....
    > >
    > > =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000>=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    >
    > --
    >
    > Dave Peterson
    >
    Reply With QuoteRegister To Reply
  4. 12-08-2005, 08:45 PM #4
    Ragdyer
    Guest

    Re: SUMPRODUCT Question....

    You can try this:

    =SUMPRODUCT((ISNUMBER(SEARCH("Charged",CaseData!$BA$2:$BA$1000)))*(CaseData!$C$2:$C$1000>=DATE(2006,1,1))*(CaseData!$C$2:$C$1000<=DATE(2006,3,31)))

    --
    HTH,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "Jeremy Ellison" <JeremyEllison@discussions.microsoft.com> wrote in message
    news:722F1B6C-6714-43B9-ADE6-D63CC9A34091@microsoft.com...
    > That worked, but I am still trying to find out how to put a wildcard into
    > the
    > formlua to look at anything beginning with charged.... charged VOCSL1
    > or
    > charged VOCSL3 or someother charged.....
    >
    > "Dave Peterson" wrote:
    >
    >> try:
    >>
    >> =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    >> --(CaseData!$C$2:$C$1000>=--"2006-01-01"),
    >> --(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    >>
    >> (the asterisks have been replaced with a comma (use your list
    >> separator).)
    >>
    >> Personally, I'd be more explicit with the dates:
    >>
    >> =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    >> --(CaseData!$C$2:$C$1000>=date(2006,1,1)),
    >> --(CaseData!$C$2:$C$1000<=date(2006,03,31))
    >>
    >> I don't trust excel to get the ymd correct.
    >>
    >>
    >>
    >> Jeremy Ellison wrote:
    >> >
    >> > I have a column (c) with dates...I have column (ba) with text data,
    >> > sometimes
    >> > RIEP sometimes Charged with (some type of crime). I want to have excel
    >> > add
    >> > up the total occurrances of RIEP between 2 dates (ie quarter 1 =
    >> > 1/1/2006 -
    >> > 3/31/2006). I am able to do this with the following formula. I then
    >> > want
    >> > excel to add up the occurances of Charged. I have tried to insert
    >> > "Charged*" ,but this does not work. Is there a better formula for this
    >> > type
    >> > of action?? I have to havce a variable because I want to be able to
    >> > have
    >> > data such as Charged with VOCSL-1 Charged with VOCSL-1 charged
    >> > with......
    >> > what ever....
    >> >
    >> > =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000>=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    >>
    >> --
    >>
    >> Dave Peterson
    >>

    Reply With QuoteRegister To Reply
  5. 12-08-2005, 09:00 PM #5
    Dave Peterson
    Guest

    Re: SUMPRODUCT Question....

    Thanks RD.

    (I missed that portion.)

    Ragdyer wrote:
    >
    > You can try this:
    >
    > =SUMPRODUCT((ISNUMBER(SEARCH("Charged",CaseData!$BA$2:$BA$1000)))*(CaseData!$C$2:$C$1000>=DATE(2006,1,1))*(CaseData!$C$2:$C$1000<=DATE(2006,3,31)))
    >
    > --
    > HTH,
    >
    > RD
    >
    > ---------------------------------------------------------------------------
    > Please keep all correspondence within the NewsGroup, so all may benefit !
    > ---------------------------------------------------------------------------
    > "Jeremy Ellison" <JeremyEllison@discussions.microsoft.com> wrote in message
    > news:722F1B6C-6714-43B9-ADE6-D63CC9A34091@microsoft.com...
    > > That worked, but I am still trying to find out how to put a wildcard into
    > > the
    > > formlua to look at anything beginning with charged.... charged VOCSL1
    > > or
    > > charged VOCSL3 or someother charged.....
    > >
    > > "Dave Peterson" wrote:
    > >
    > >> try:
    > >>
    > >> =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    > >> --(CaseData!$C$2:$C$1000>=--"2006-01-01"),
    > >> --(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    > >>
    > >> (the asterisks have been replaced with a comma (use your list
    > >> separator).)
    > >>
    > >> Personally, I'd be more explicit with the dates:
    > >>
    > >> =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    > >> --(CaseData!$C$2:$C$1000>=date(2006,1,1)),
    > >> --(CaseData!$C$2:$C$1000<=date(2006,03,31))
    > >>
    > >> I don't trust excel to get the ymd correct.
    > >>
    > >>
    > >>
    > >> Jeremy Ellison wrote:
    > >> >
    > >> > I have a column (c) with dates...I have column (ba) with text data,
    > >> > sometimes
    > >> > RIEP sometimes Charged with (some type of crime). I want to have excel
    > >> > add
    > >> > up the total occurrances of RIEP between 2 dates (ie quarter 1 =
    > >> > 1/1/2006 -
    > >> > 3/31/2006). I am able to do this with the following formula. I then
    > >> > want
    > >> > excel to add up the occurances of Charged. I have tried to insert
    > >> > "Charged*" ,but this does not work. Is there a better formula for this
    > >> > type
    > >> > of action?? I have to havce a variable because I want to be able to
    > >> > have
    > >> > data such as Charged with VOCSL-1 Charged with VOCSL-1 charged
    > >> > with......
    > >> > what ever....
    > >> >
    > >> > =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000>=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    > >>
    > >> --
    > >>
    > >> Dave Peterson
    > >>

    --

    Dave Peterson
    Reply With QuoteRegister To Reply
  6. 12-08-2005, 09:10 PM #6
    Ragdyer
    Guest

    Re: SUMPRODUCT Question....

    I thought you were done for the day.<g>

    Looks like you're trying to duplicate Frank's old habits (God rest his
    soul).
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
    news:4398D510.7DFF630E@verizonXSPAM.net...
    > Thanks RD.
    >
    > (I missed that portion.)
    >
    > Ragdyer wrote:
    >>
    >> You can try this:
    >>
    >> =SUMPRODUCT((ISNUMBER(SEARCH("Charged",CaseData!$BA$2:$BA$1000)))*(CaseData!$C$2:$C$1000>=DATE(2006,1,1))*(CaseData!$C$2:$C$1000<=DATE(2006,3,31)))
    >>
    >> --
    >> HTH,
    >>
    >> RD
    >>
    >> ---------------------------------------------------------------------------
    >> Please keep all correspondence within the NewsGroup, so all may benefit !
    >> ---------------------------------------------------------------------------
    >> "Jeremy Ellison" <JeremyEllison@discussions.microsoft.com> wrote in
    >> message
    >> news:722F1B6C-6714-43B9-ADE6-D63CC9A34091@microsoft.com...
    >> > That worked, but I am still trying to find out how to put a wildcard
    >> > into
    >> > the
    >> > formlua to look at anything beginning with charged.... charged
    >> > VOCSL1
    >> > or
    >> > charged VOCSL3 or someother charged.....
    >> >
    >> > "Dave Peterson" wrote:
    >> >
    >> >> try:
    >> >>
    >> >> =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    >> >> --(CaseData!$C$2:$C$1000>=--"2006-01-01"),
    >> >> --(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    >> >>
    >> >> (the asterisks have been replaced with a comma (use your list
    >> >> separator).)
    >> >>
    >> >> Personally, I'd be more explicit with the dates:
    >> >>
    >> >> =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"),
    >> >> --(CaseData!$C$2:$C$1000>=date(2006,1,1)),
    >> >> --(CaseData!$C$2:$C$1000<=date(2006,03,31))
    >> >>
    >> >> I don't trust excel to get the ymd correct.
    >> >>
    >> >>
    >> >>
    >> >> Jeremy Ellison wrote:
    >> >> >
    >> >> > I have a column (c) with dates...I have column (ba) with text data,
    >> >> > sometimes
    >> >> > RIEP sometimes Charged with (some type of crime). I want to have
    >> >> > excel
    >> >> > add
    >> >> > up the total occurrances of RIEP between 2 dates (ie quarter 1 =
    >> >> > 1/1/2006 -
    >> >> > 3/31/2006). I am able to do this with the following formula. I
    >> >> > then
    >> >> > want
    >> >> > excel to add up the occurances of Charged. I have tried to insert
    >> >> > "Charged*" ,but this does not work. Is there a better formula for
    >> >> > this
    >> >> > type
    >> >> > of action?? I have to havce a variable because I want to be able to
    >> >> > have
    >> >> > data such as Charged with VOCSL-1 Charged with VOCSL-1 charged
    >> >> > with......
    >> >> > what ever....
    >> >> >
    >> >> > =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000>=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31"))
    >> >>
    >> >> --
    >> >>
    >> >> Dave Peterson
    >> >>
    >
    > --
    >
    > Dave Peterson

    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1