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

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

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

=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

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