| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
SUMIF positive values only from database
I want to sum all the positive values from a database for a single criteria. e.g. database is, fruit buy/sell quantity apples -5 oranges 2 apples 3 apples 1 I want to sum all the positive values only for apples. (Note my real database has many more entries than this). so I am sumif(database, "apples", quantity) which returns -1 what can I do to just get the positive numbers of apples ?? Help Please :) -- gmac ------------------------------------------------------------------------ gmac's Profile: http://www.excelforum.com/member.php...o&userid=23224 View this thread: http://www.excelforum.com/showthread...hreadid=383010 |
|
#2
|
|||
|
|||
|
Enter the particular fruit that you're looking to count into C1, then try
this: =SUMPRODUCT((A2:A100=C1)*(B2:B1000)*B2:B100) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "gmac" wrote in message ... I want to sum all the positive values from a database for a single criteria. e.g. database is, fruit buy/sell quantity apples -5 oranges 2 apples 3 apples 1 I want to sum all the positive values only for apples. (Note my real database has many more entries than this). so I am sumif(database, "apples", quantity) which returns -1 what can I do to just get the positive numbers of apples ?? Help Please :) -- gmac ------------------------------------------------------------------------ gmac's Profile: http://www.excelforum.com/member.php...o&userid=23224 View this thread: http://www.excelforum.com/showthread...hreadid=383010 |
|
#3
|
|||
|
|||
|
Hi!
Try this: =SUMPRODUCT(--(A1:A4="apples"),--(B1:B40),B1:B4) Or, use a cell to hold apples: D1 = apples =SUMPRODUCT(--(A1:A4=D1),--(B1:B40),B1:B4) Biff "gmac" wrote in message ... I want to sum all the positive values from a database for a single criteria. e.g. database is, fruit buy/sell quantity apples -5 oranges 2 apples 3 apples 1 I want to sum all the positive values only for apples. (Note my real database has many more entries than this). so I am sumif(database, "apples", quantity) which returns -1 what can I do to just get the positive numbers of apples ?? Help Please :) -- gmac ------------------------------------------------------------------------ gmac's Profile: http://www.excelforum.com/member.php...o&userid=23224 View this thread: http://www.excelforum.com/showthread...hreadid=383010 |
|
#4
|
|||
|
|||
|
=sumproduct((a1:a5="apples")*(b1:b5=0)*(b1:b5)) sums items in b1:b5 =0 where corresponding entry in column a is "apples" -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=383010 |
|
#5
|
|||
|
|||
|
Thanks guys, they both work. SUMPRODUCT and some brains go along way. Thanks again. -- gmac ------------------------------------------------------------------------ gmac's Profile: http://www.excelforum.com/member.php...o&userid=23224 View this thread: http://www.excelforum.com/showthread...hreadid=383010 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
| SUMIF function referring to values on different Worksheet | Excel Worksheet Functions | |||
| switching values from positive to negative | Excel Worksheet Functions | |||
| How can I get Positive values only from the random number generat. | Excel Discussion (Misc queries) | |||
| SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions | |||
Linear Mode
