Sum If function with 2 criteria

[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. 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

 

[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. Valko]

To *COUNT* buy/sell:

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

Biff

 

[Khaled]

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