Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    selectively add from a range (Excel 2003)

    I'm trying to come up with a formula that will total a set of numbers based on a quantity chosen. The data looks like this:
    <pre>Qty Cost
    0 100
    2 150
    -1 75
    4 250
    0 125</pre>

    Where qty=0 means it wasn't selected, qty=-1 means it's not a valid selection (so ignore it always). In this sample the total cost of the chosen items would be

    0*100 + 2*150 + 4*250 + 0*125 = $1300

    I need it to work for any Qtry combo of 0, -1, n since this is a lookup table that drives a list box somewhere else in the workbook (the list box will prevent someone from selecting an item with qty=-1).

    I tried SUMIF but couldn't get it to work right.

    Thnx, Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selectively add from a range (Excel 2003)

    Say that your table is A1:B6 (with the headers in row 1). You can use

    =SUMPRODUCT(A2:A6*B2:B6*(A2:A6>0))

    or as an array formula (confirmed with Ctrl+Shift+Enter)

    =SUM(IF(A2:A6>0,A2:A6*B2:B6))

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: selectively add from a range (Excel 2003)

    Deb, this should work as an array formula (assuming your data is in the ranges shown):

    =SUM(IF(A2:A6>0,A2:A6*B2:B6,))

    Enter by using Ctrl-Shift-Enter.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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