+ Reply to Thread
Results 1 to 9 of 9

SUMIF with 2 conditions

  1. #1
    Registered User
    Join Date
    01-04-2006
    Posts
    3

    SUMIF with 2 conditions

    Can anyone help, I have a spreadsheet with 2 sheets, the first sheet contains the data, the second sheet is a summary page.

    Sheet 1 contains 8 colums (a - h), colum f contains price information, colum g contains the department number and colum h contains the product code.

    What I would like to do on the summary sheet is have a cell where you can input a deprtment number and then a list of all the product codes with a formula to work out the price of each of the products for that deprtment number.

    I can work out, with " =SUMIF('Stock Issues'!H:H,A2,'Stock Issues'!F:F) " the totals for each product code, but is it possible to add the department condition?

  2. #2
    Bob Phillips
    Guest

    Re: SUMIF with 2 conditions

    =SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),--('Stock
    Issues'H2:H200=B1),'Stock Issues'F2:F200)

    and copy down, where A1 has the department number, B1:Bn has the product
    codes. Note that SUMPRODUCT cannot use whole columns, only a specified
    range.

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "1990" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Can anyone help, I have a spreadsheet with 2 sheets, the first sheet
    > contains the data, the second sheet is a summary page.
    >
    > Sheet 1 contains 8 colums (a - h), colum f contains price information,
    > colum g contains the department number and colum h contains the product
    > code.
    >
    > What I would like to do on the summary sheet is have a cell where you
    > can input a deprtment number and then a list of all the product codes
    > with a formula to work out the price of each of the products for that
    > deprtment number.
    >
    > I can work out, with " =SUMIF('Stock Issues'!H:H,A2,'Stock
    > Issues'!F:F) " the totals for each product code, but is it possible to
    > add the department condition?
    >
    >
    > --
    > 1990
    > ------------------------------------------------------------------------
    > 1990's Profile:

    http://www.excelforum.com/member.php...o&userid=30100
    > View this thread: http://www.excelforum.com/showthread...hreadid=497851
    >




  3. #3
    Registered User
    Join Date
    01-04-2006
    Posts
    3
    Thanks for the reply, but when i copy that in it brings up an error message and highlights the second 'Stock - highlighted in red here " =SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stock Issues'H2:H200=$A$1),'Stock Issues'F2:F200) "

  4. #4
    Bob Phillips
    Guest

    Re: SUMIF with 2 conditions

    Maybe wrap-around.

    Try copying

    =SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),
    --('Stock Issues'H2:H200=B1),'Stock Issues'F2:F200)


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "1990" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Thanks for the reply, but when i copy that in it brings up an error
    > message and highlights the second 'Stock - highlighted in red here "
    > =SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stock
    > Issues'H2:H200=$A$1),'Stock Issues'F2:F200) "
    >
    >
    > --
    > 1990
    > ------------------------------------------------------------------------
    > 1990's Profile:

    http://www.excelforum.com/member.php...o&userid=30100
    > View this thread: http://www.excelforum.com/showthread...hreadid=497851
    >




  5. #5
    Forum Contributor
    Join Date
    11-29-2005
    Posts
    142
    Quote Originally Posted by Bob Phillips
    Maybe wrap-around.

    Try copying

    =SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),
    --('Stock Issues'H2:H200=B1),'Stock Issues'F2:F200)


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "1990" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Thanks for the reply, but when i copy that in it brings up an error
    > message and highlights the second 'Stock - highlighted in red here "
    > =SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stock
    > Issues'H2:H200=$A$1),'Stock Issues'F2:F200) "
    >
    >
    > --
    > 1990
    > ------------------------------------------------------------------------
    > 1990's Profile:

    http://www.excelforum.com/member.php...o&userid=30100
    > View this thread: http://www.excelforum.com/showthread...hreadid=497851
    >

    The exclamation points are necessary after each reference to a worksheet.

    =SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),--('Stock Issues'!H2:H200=B1),'Stock Issues'!F2:F200)

  6. #6
    Bob Phillips
    Guest

    Re: SUMIF with 2 conditions

    Oops, didn't even notice I had dropped those in the copy.

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Vito" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Bob Phillips Wrote:
    > > Maybe wrap-around.
    > >
    > > Try copying
    > >
    > > =SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),
    > > --('Stock Issues'H2:H200=B1),'Stock Issues'F2:F200)
    > >
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (remove nothere from email address if mailing direct)
    > >
    > > "1990" <[email protected]> wrote in
    > > message
    > > news:[email protected]...
    > > >
    > > > Thanks for the reply, but when i copy that in it brings up an error
    > > > message and highlights the second 'Stock - highlighted in red here

    > > "
    > > > =SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stock
    > > > Issues'H2:H200=$A$1),'Stock Issues'F2:F200) "
    > > >
    > > >
    > > > --
    > > > 1990
    > > >

    > > ------------------------------------------------------------------------
    > > > 1990's Profile:

    > > http://www.excelforum.com/member.php...o&userid=30100
    > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=497851
    > > >

    >
    >
    > The exclamation points are necessary after *each* reference to a
    > worksheet.
    >
    > =SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),--('Stock
    > Issues'!H2:H200=B1),'Stock Issues'!F2:F200)
    >
    >
    > --
    > Vito
    > ------------------------------------------------------------------------
    > Vito's Profile:

    http://www.excelforum.com/member.php...o&userid=29182
    > View this thread: http://www.excelforum.com/showthread...hreadid=497851
    >




  7. #7
    Registered User
    Join Date
    01-04-2006
    Posts
    3
    Spot on, thanks.

  8. #8
    ellebelle
    Guest

    Re: SUMIF with 2 conditions

    what does the symbol below mean in your formula above
    --

    i don't have it?

    "1990" wrote:

    >
    > Spot on, thanks.
    >
    >
    > --
    > 1990
    > ------------------------------------------------------------------------
    > 1990's Profile: http://www.excelforum.com/member.php...o&userid=30100
    > View this thread: http://www.excelforum.com/showthread...hreadid=497851
    >
    >


  9. #9
    Kleev
    Guest

    Re: SUMIF with 2 conditions

    That's 2 minus signs (double unary operator.) They are used to coerce
    logical true and false values to 1s and 0s. Let me see if I can find the
    link to Bob's site which explains sumproduct very well:
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    "ellebelle" wrote:

    > what does the symbol below mean in your formula above
    > --
    >
    > i don't have it?
    >
    > "1990" wrote:
    >
    > >
    > > Spot on, thanks.
    > >
    > >
    > > --
    > > 1990
    > > ------------------------------------------------------------------------
    > > 1990's Profile: http://www.excelforum.com/member.php...o&userid=30100
    > > View this thread: http://www.excelforum.com/showthread...hreadid=497851
    > >
    > >


+ 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