Sum If function with 2 criteria

K

khaled shaheen

Hi all,
First here below my example in order to be understood easily
Database:
Stock ticker Quantity Buy/Sell
HRHO 1000 Buy
ESRS 500 Buy
HRHO 500 Sell

Inquiry:
Ticker Total Bought Total Sold
HRHO ??? ???

I want to know how to get the total quantity sold for HRHO alone, by using
Sumif function it gives me total quatity (bought + sold) for HRHO together.
Appreciate your support,

Khaled
 
T

T. Valko

Try this:

=SUMPRODUCT(--(A2:A4="HRHO"),--(C2:C4="BUY"),B2:B4)

Better to use cells to hold the criteria:

..............E..............F.........G
1.......Ticker........Buy......Sell
2.......HRHO........................

Entered in F2 and copied across then down as needed:

=SUMPRODUCT(--($A$2:$A$4=$E2),--($C$2:$C$4=F$1),$B$2:$B$4)

Biff
 
K

khaled shaheen

thanks a million, it worked with me but i have another problem
using the same example, this time i need to COUNT # of selling transactions
alone which means that i need to count based on 2 criteria "HRHO" and "Sell",
then "HRHO" and "Buy"
Database:
Stock ticker Quantity Buy/Sell
HRHO 1000 Buy
ESRS 500 Buy
HRHO 500 Sell

Inquiry: Ticker # of Transactions"Buy" # of
transactions "Sell"
HRHO ???
???
Kind regards,

Khaled
 
T

T. Valko

To *COUNT* buy/sell:

=SUMPRODUCT(--(A2:A4="HRHO"),--(C2:C4="BUY"))
=SUMPRODUCT(--(A2:A4="HRHO"),--(C2:C4="SELL"))

Biff
 
K

Khaled

khaled shaheen said:
Hi all,
First here below my example in order to be understood easily
Database:
Stock ticker Quantity Buy/Sell
HRHO 1000 Buy
ESRS 500 Buy
HRHO 500 Sell

Inquiry:
Ticker Total Bought Total Sold
HRHO ??? ???

I want to know how to get the total quantity sold for HRHO alone, by using
Sumif function it gives me total quatity (bought + sold) for HRHO together.
Appreciate your support,

Khaled
Make new colum with with the ticker and the buy/sell with and function
like this : HRHO&buy (you can name it "ticket&kind") an hide it
use this colum in the sumif function istead of the ticket colum with the
criteria "hrho"&"buy"
I hope it helps
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top