+ Reply to Thread
Results 1 to 10 of 10

=SUMIF

  1. #1
    He4Giv
    Guest

    =SUMIF

    Hello
    I want to put my formula on sheet1 and reference columns on sheet2 for example
    =sumif(Sheet2!H2:H15=1,and if sheet2!J2:J15=9,sheet2!g2:g15))
    Basically I want to have the sumif check column H for the #1 AND aslo check
    Column J for the #9 and if it finds both numbers in any one row to grab the
    figure from the G column and put it on Sheet1.
    I tried different variations and I get either #REF or #NAME and sometimes
    the "File not found" window pops up when I try to place the formula.
    I know how to write a one senaro SUMIF but I get confused when I want it to
    check two columns and if it finds the #1 and the #9 in two columns to carry
    out the SUMIF.
    Thank you
    --
    He4Giv (****)

  2. #2
    Jason Morin
    Guest

    Re: =SUMIF

    Try SUMPRODUCT:

    =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)

    HTH
    Jason
    Atlanta, GA

    >-----Original Message-----
    >Hello
    >I want to put my formula on sheet1 and reference columns

    on sheet2 for example
    >=sumif(Sheet2!H2:H15=1,and if sheet2!J2:J15=9,sheet2!

    g2:g15))
    >Basically I want to have the sumif check column H for

    the #1 AND aslo check
    >Column J for the #9 and if it finds both numbers in any

    one row to grab the
    >figure from the G column and put it on Sheet1.
    >I tried different variations and I get either #REF or

    #NAME and sometimes
    >the "File not found" window pops up when I try to place

    the formula.
    >I know how to write a one senaro SUMIF but I get

    confused when I want it to
    >check two columns and if it finds the #1 and the #9 in

    two columns to carry
    >out the SUMIF.
    >Thank you
    >--
    >He4Giv (****)
    >.
    >


  3. #3
    He4Giv
    Guest

    Re: =SUMIF

    I pated your formula into the cell and edited Sheet2 to read the name on my
    tab which is BARLSIT LOG. When I did so the "File not found" window came up
    like its looking for a file and in the cell it pasted #NAME?

    "Jason Morin" wrote:

    > Try SUMPRODUCT:
    >
    > =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)
    >
    > HTH
    > Jason
    > Atlanta, GA
    >
    > >-----Original Message-----
    > >Hello
    > >I want to put my formula on sheet1 and reference columns

    > on sheet2 for example
    > >=sumif(Sheet2!H2:H15=1,and if sheet2!J2:J15=9,sheet2!

    > g2:g15))
    > >Basically I want to have the sumif check column H for

    > the #1 AND aslo check
    > >Column J for the #9 and if it finds both numbers in any

    > one row to grab the
    > >figure from the G column and put it on Sheet1.
    > >I tried different variations and I get either #REF or

    > #NAME and sometimes
    > >the "File not found" window pops up when I try to place

    > the formula.
    > >I know how to write a one senaro SUMIF but I get

    > confused when I want it to
    > >check two columns and if it finds the #1 and the #9 in

    > two columns to carry
    > >out the SUMIF.
    > >Thank you
    > >--
    > >He4Giv (****)
    > >.
    > >

    >


  4. #4
    SongBear
    Guest

    RE: =SUMIF

    =SUM(IF(Sheet3!$A$29:$A$34=1,IF(Sheet3!$C$29:$C$34=9,Sheet3!$B$29:$B$34,0),0))
    this formula was on sheet one, the table was on sheet three:

    one two three

    1 12 9


    1 14
    15 9
    The formula returned 12.

    I used an Excel wizard called the 'conditional sum wizard'. yoiu have to go
    to Tools|Addins to see if you have it to install. If it is in the list of
    available addins, check the box and OK.

    Bear

    "He4Giv" wrote:

    > Hello
    > I want to put my formula on sheet1 and reference columns on sheet2 for example
    > =sumif(Sheet2!H2:H15=1,and if sheet2!J2:J15=9,sheet2!g2:g15))
    > Basically I want to have the sumif check column H for the #1 AND aslo check
    > Column J for the #9 and if it finds both numbers in any one row to grab the
    > figure from the G column and put it on Sheet1.
    > I tried different variations and I get either #REF or #NAME and sometimes
    > the "File not found" window pops up when I try to place the formula.
    > I know how to write a one senaro SUMIF but I get confused when I want it to
    > check two columns and if it finds the #1 and the #9 in two columns to carry
    > out the SUMIF.
    > Thank you
    > --
    > He4Giv (****)


  5. #5
    Jason Morin
    Guest

    Re: =SUMIF

    You probably left out the apostrophes that surround the
    sheet name (Excel includes them when there is a space in
    the sheet name). Rather than editing my formula, re-
    create it and click on the ranges - Excel will fill in
    the sheet name properly.

    Jason

    >-----Original Message-----
    >I pated your formula into the cell and edited Sheet2 to

    read the name on my
    >tab which is BARLSIT LOG. When I did so the "File not

    found" window came up
    >like its looking for a file and in the cell it pasted

    #NAME?
    >
    >"Jason Morin" wrote:
    >
    >> Try SUMPRODUCT:
    >>
    >> =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)
    >>
    >> HTH
    >> Jason
    >> Atlanta, GA
    >>
    >> >-----Original Message-----
    >> >Hello
    >> >I want to put my formula on sheet1 and reference

    columns
    >> on sheet2 for example
    >> >=sumif(Sheet2!H2:H15=1,and if sheet2!J2:J15=9,sheet2!

    >> g2:g15))
    >> >Basically I want to have the sumif check column H for

    >> the #1 AND aslo check
    >> >Column J for the #9 and if it finds both numbers in

    any
    >> one row to grab the
    >> >figure from the G column and put it on Sheet1.
    >> >I tried different variations and I get either #REF or

    >> #NAME and sometimes
    >> >the "File not found" window pops up when I try to

    place
    >> the formula.
    >> >I know how to write a one senaro SUMIF but I get

    >> confused when I want it to
    >> >check two columns and if it finds the #1 and the #9

    in
    >> two columns to carry
    >> >out the SUMIF.
    >> >Thank you
    >> >--
    >> >He4Giv (****)
    >> >.
    >> >

    >>

    >.
    >


  6. #6
    Don Guillett
    Guest

    Re: =SUMIF

    try
    BARLSIT LOG
    ' BARLSIT LOG'

    --
    Don Guillett
    SalesAid Software
    donaldb@281.com
    "He4Giv" <He4Giv@discussions.microsoft.com> wrote in message
    news:C8034169-B78F-44DB-B465-628CD1E73AD0@microsoft.com...
    > I pated your formula into the cell and edited Sheet2 to read the name on

    my
    > tab which is BARLSIT LOG. When I did so the "File not found" window came

    up
    > like its looking for a file and in the cell it pasted #NAME?
    >
    > "Jason Morin" wrote:
    >
    > > Try SUMPRODUCT:
    > >
    > > =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)
    > >
    > > HTH
    > > Jason
    > > Atlanta, GA
    > >
    > > >-----Original Message-----
    > > >Hello
    > > >I want to put my formula on sheet1 and reference columns

    > > on sheet2 for example
    > > >=sumif(Sheet2!H2:H15=1,and if sheet2!J2:J15=9,sheet2!

    > > g2:g15))
    > > >Basically I want to have the sumif check column H for

    > > the #1 AND aslo check
    > > >Column J for the #9 and if it finds both numbers in any

    > > one row to grab the
    > > >figure from the G column and put it on Sheet1.
    > > >I tried different variations and I get either #REF or

    > > #NAME and sometimes
    > > >the "File not found" window pops up when I try to place

    > > the formula.
    > > >I know how to write a one senaro SUMIF but I get

    > > confused when I want it to
    > > >check two columns and if it finds the #1 and the #9 in

    > > two columns to carry
    > > >out the SUMIF.
    > > >Thank you
    > > >--
    > > >He4Giv (****)
    > > >.
    > > >

    > >




  7. #7
    Max
    Guest

    Re: =SUMIF

    "He4Giv" <He4Giv@discussions.microsoft.com> wrote
    > I pated your formula into the cell and edited Sheet2 to read the name on

    my
    > tab which is BARLSIT LOG. When I did so the "File not found" window came

    up
    > like its looking for a file and in the cell it pasted #NAME?


    Think it's looking for "Sheet2",
    which presumably doesn't exist in your book.

    Its usually easier to rename your sheetname to suit the formula suggested
    first, e.g.: rename "BARLSIT LOG" to "Sheet2", then paste-in the formula and
    get it working fine.

    And then you could rename the sheet back to its original sheetname, and
    leave it to Excel to auto-adjust the formula, which would happen in this
    case.
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  8. #8
    He4Giv
    Guest

    Re: =SUMIF

    the sumproduct worked great when i added the tick marks each end, ie:'BARLIST
    LOG' and I forgot that I needed to hit CTRL+SHIFT+ENTER since its an array
    formula.
    question: If I wanted to add a 3rd senaro, or thrid column How would you
    write it?
    The thrid condition would be 'Barlist Log'!I2:I15,"BLK"

    "Jason Morin" wrote:

    > You probably left out the apostrophes that surround the
    > sheet name (Excel includes them when there is a space in
    > the sheet name). Rather than editing my formula, re-
    > create it and click on the ranges - Excel will fill in
    > the sheet name properly.
    >
    > Jason
    >
    > >-----Original Message-----
    > >I pated your formula into the cell and edited Sheet2 to

    > read the name on my
    > >tab which is BARLSIT LOG. When I did so the "File not

    > found" window came up
    > >like its looking for a file and in the cell it pasted

    > #NAME?
    > >
    > >"Jason Morin" wrote:
    > >
    > >> Try SUMPRODUCT:
    > >>
    > >> =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)
    > >>
    > >> HTH
    > >> Jason
    > >> Atlanta, GA
    > >>
    > >> >-----Original Message-----
    > >> >Hello
    > >> >I want to put my formula on sheet1 and reference

    > columns
    > >> on sheet2 for example
    > >> >=sumif(Sheet2!H2:H15=1,and if sheet2!J2:J15=9,sheet2!
    > >> g2:g15))
    > >> >Basically I want to have the sumif check column H for
    > >> the #1 AND aslo check
    > >> >Column J for the #9 and if it finds both numbers in

    > any
    > >> one row to grab the
    > >> >figure from the G column and put it on Sheet1.
    > >> >I tried different variations and I get either #REF or
    > >> #NAME and sometimes
    > >> >the "File not found" window pops up when I try to

    > place
    > >> the formula.
    > >> >I know how to write a one senaro SUMIF but I get
    > >> confused when I want it to
    > >> >check two columns and if it finds the #1 and the #9

    > in
    > >> two columns to carry
    > >> >out the SUMIF.
    > >> >Thank you
    > >> >--
    > >> >He4Giv (****)
    > >> >.
    > >> >
    > >>

    > >.
    > >

    >


  9. #9
    Max
    Guest

    Re: =SUMIF

    > ... I needed to hit CTRL+SHIFT+ENTER
    > since its an array formula.


    For SUMPRODUCT?
    Think just a normal press ENTER should do it

    > The thrid condition would be 'Barlist Log'!I2:I15,"BLK"


    Just "add-on" the criteria in the formula, like :

    =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*(Sheet2!I2:I15="BLK")*G2:G15
    )

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "He4Giv" <He4Giv@discussions.microsoft.com> wrote in message
    news:8519134F-3443-4274-8296-91E465C28D0A@microsoft.com...
    > the sumproduct worked great when i added the tick marks each end,

    ie:'BARLIST
    > LOG' and I forgot that I needed to hit CTRL+SHIFT+ENTER since its an array
    > formula.
    > question: If I wanted to add a 3rd senaro, or thrid column How would you
    > write it?




  10. #10
    Biff
    Guest

    Re: =SUMIF

    Hi!

    =SUMPRODUCT(('Barlist Log'!H2:H15=1)*('Barlist Log'!
    I2:I15="blk")*('Barlist Log'!J2:J15=9)*G2:G15)

    No need to enter as an array formula. The SUMPRODUCT
    function accepts arrays as arguments.

    Biff

    >-----Original Message-----
    >the sumproduct worked great when i added the tick marks

    each end, ie:'BARLIST
    >LOG' and I forgot that I needed to hit CTRL+SHIFT+ENTER

    since its an array
    >formula.
    >question: If I wanted to add a 3rd senaro, or thrid

    column How would you
    >write it?
    >The thrid condition would be 'Barlist Log'!I2:I15,"BLK"
    >
    >"Jason Morin" wrote:
    >
    >> You probably left out the apostrophes that surround the
    >> sheet name (Excel includes them when there is a space

    in
    >> the sheet name). Rather than editing my formula, re-
    >> create it and click on the ranges - Excel will fill in
    >> the sheet name properly.
    >>
    >> Jason
    >>
    >> >-----Original Message-----
    >> >I pated your formula into the cell and edited Sheet2

    to
    >> read the name on my
    >> >tab which is BARLSIT LOG. When I did so the "File not

    >> found" window came up
    >> >like its looking for a file and in the cell it pasted

    >> #NAME?
    >> >
    >> >"Jason Morin" wrote:
    >> >
    >> >> Try SUMPRODUCT:
    >> >>
    >> >> =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)

    *G2:G15)
    >> >>
    >> >> HTH
    >> >> Jason
    >> >> Atlanta, GA
    >> >>
    >> >> >-----Original Message-----
    >> >> >Hello
    >> >> >I want to put my formula on sheet1 and reference

    >> columns
    >> >> on sheet2 for example
    >> >> >=sumif(Sheet2!H2:H15=1,and if sheet2!

    J2:J15=9,sheet2!
    >> >> g2:g15))
    >> >> >Basically I want to have the sumif check column H

    for
    >> >> the #1 AND aslo check
    >> >> >Column J for the #9 and if it finds both numbers in

    >> any
    >> >> one row to grab the
    >> >> >figure from the G column and put it on Sheet1.
    >> >> >I tried different variations and I get either #REF

    or
    >> >> #NAME and sometimes
    >> >> >the "File not found" window pops up when I try to

    >> place
    >> >> the formula.
    >> >> >I know how to write a one senaro SUMIF but I get
    >> >> confused when I want it to
    >> >> >check two columns and if it finds the #1 and the #9

    >> in
    >> >> two columns to carry
    >> >> >out the SUMIF.
    >> >> >Thank you
    >> >> >--
    >> >> >He4Giv (****)
    >> >> >.
    >> >> >
    >> >>
    >> >.
    >> >

    >>

    >.
    >


+ 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