+ Reply to Thread
Results 1 to 5 of 5

sum column when it meets certain conditions

  1. #1
    jimswinder
    Guest

    sum column when it meets certain conditions

    I am trying to sum a column when it meets two conditions. I have tried the
    following:

    =IF('Service Request Log'!$B$4:$B$5000,$A189,SUMPRODUCT(--(LEFT('Service
    Request Log'!M$4:M$5000,3)="asa")))

    But it is totaling the column whether or not it meets the first condition of
    ('Service Request Log'!$B$4:$B$5000,$A189).

    Any suggestions?

    Thanks...

  2. #2
    Bondi
    Guest

    Re: sum column when it meets certain conditions


    jimswinder wrote:
    > I am trying to sum a column when it meets two conditions. I have tried the
    > following:
    >
    > =IF('Service Request Log'!$B$4:$B$5000,$A189,SUMPRODUCT(--(LEFT('Service
    > Request Log'!M$4:M$5000,3)="asa")))
    >
    > But it is totaling the column whether or not it meets the first condition of
    > ('Service Request Log'!$B$4:$B$5000,$A189).
    >
    > Any suggestions?
    >
    > Thanks...


    Hi Jim,

    Maybe the information on this site will be helpful for you:

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Regards,
    Bondi


  3. #3
    jimswinder
    Guest

    Re: sum column when it meets certain conditions

    Sorry...read the whole thing and tried different formulas (see below)...still
    can't get it to come up with the correct result.

    =SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),(LEFT('Service
    Request Log'!M4:M5000,3)="asa"))

    =SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A190),('Service Request
    Log'!$M$4:$M$5000))


    "Bondi" wrote:

    >
    > jimswinder wrote:
    > > I am trying to sum a column when it meets two conditions. I have tried the
    > > following:
    > >
    > > =IF('Service Request Log'!$B$4:$B$5000,$A189,SUMPRODUCT(--(LEFT('Service
    > > Request Log'!M$4:M$5000,3)="asa")))
    > >
    > > But it is totaling the column whether or not it meets the first condition of
    > > ('Service Request Log'!$B$4:$B$5000,$A189).
    > >
    > > Any suggestions?
    > >
    > > Thanks...

    >
    > Hi Jim,
    >
    > Maybe the information on this site will be helpful for you:
    >
    > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    >
    > Regards,
    > Bondi
    >
    >


  4. #4
    jimswinder
    Guest

    Re: sum column when it meets certain conditions

    I found my problem...I had a comma where I should have had a "*".

    =SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A190)*(LEFT('Service
    Request Log'!M$4:M$5000,3)="asa"))

    thanks for the help. :-)

    "jimswinder" wrote:

    > Sorry...read the whole thing and tried different formulas (see below)...still
    > can't get it to come up with the correct result.
    >
    > =SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),(LEFT('Service
    > Request Log'!M4:M5000,3)="asa"))
    >
    > =SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A190),('Service Request
    > Log'!$M$4:$M$5000))
    >
    >
    > "Bondi" wrote:
    >
    > >
    > > jimswinder wrote:
    > > > I am trying to sum a column when it meets two conditions. I have tried the
    > > > following:
    > > >
    > > > =IF('Service Request Log'!$B$4:$B$5000,$A189,SUMPRODUCT(--(LEFT('Service
    > > > Request Log'!M$4:M$5000,3)="asa")))
    > > >
    > > > But it is totaling the column whether or not it meets the first condition of
    > > > ('Service Request Log'!$B$4:$B$5000,$A189).
    > > >
    > > > Any suggestions?
    > > >
    > > > Thanks...

    > >
    > > Hi Jim,
    > >
    > > Maybe the information on this site will be helpful for you:
    > >
    > > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    > >
    > > Regards,
    > > Bondi
    > >
    > >


  5. #5
    Bondi
    Guest

    Re: sum column when it meets certain conditions


    jimswinder wrote:
    > I found my problem...I had a comma where I should have had a "*".
    >
    > =SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A190)*(LEFT('Service
    > Request Log'!M$4:M$5000,3)="asa"))
    >
    > thanks for the help. :-)
    >
    > "jimswinder" wrote:
    >
    > > Sorry...read the whole thing and tried different formulas (see below)...still
    > > can't get it to come up with the correct result.
    > >
    > > =SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),(LEFT('Service
    > > Request Log'!M4:M5000,3)="asa"))
    > >
    > > =SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A190),('Service Request
    > > Log'!$M$4:$M$5000))
    > >
    > >
    > > "Bondi" wrote:
    > >
    > > >
    > > > jimswinder wrote:
    > > > > I am trying to sum a column when it meets two conditions. I have tried the
    > > > > following:
    > > > >
    > > > > =IF('Service Request Log'!$B$4:$B$5000,$A189,SUMPRODUCT(--(LEFT('Service
    > > > > Request Log'!M$4:M$5000,3)="asa")))
    > > > >
    > > > > But it is totaling the column whether or not it meets the first condition of
    > > > > ('Service Request Log'!$B$4:$B$5000,$A189).
    > > > >
    > > > > Any suggestions?
    > > > >
    > > > > Thanks...
    > > >
    > > > Hi Jim,
    > > >
    > > > Maybe the information on this site will be helpful for you:
    > > >
    > > > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    > > >
    > > > Regards,
    > > > Bondi
    > > >
    > > >


    Good stuff,

    Regards and best of luck,
    Bondi


+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1