# Thread: selectively add from a range (Excel 2003)

1. ## 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. ## 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. ## 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.

#### Posting Permissions

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