Sumif function with two criterias

[ABRAR]

I have some stock names in Column A, I have some "Buy" or "sell" in column B,
I have Transaction amount written in Column C

I will specify the stock name in Cell D1, I want the total "Buy" amount for
that particular stock in Cell D2. For this there should be a function in Cell
D2 which adds values in Column C if the Stock name in Column A is equal to
Cell D1 and has "Buy" written in Column B

Example

GESCOR Buy 250
LARTOU Sell 40
GVKPOW Buy 1000
ENGIND Sell 101
RELCOM Sell 200
TATPOW Sell 10
RELIND Sell 10
TISCO Sell 350
RELCOM Sell 100
SESGOA Buy 250
TATPOW Sell 20
INFTEC Sell 40
RELPOW Buy 200
RELCOM Sell 100
TATPOW Buy 25
BHATE Buy 35
ENGIND Buy 101
TATPOW Buy 25
RELCOM Buy 100


Cell D1-- If I input TATPOW then Cell D2 should give me value 50

 

[Pecoflyer]

I have some stock names in Column A, I have some "Buy" or "sell" i
column B,
I have Transaction amount written in Column C

I will specify the stock name in Cell D1, I want the total "Buy" amoun
for
that particular stock in Cell D2. For this there should be a functio
in Cell
D2 which adds values in Column C if the Stock name in Column A is equa
to
Cell D1 and has "Buy" written in Column B

Example

GESCOR Buy 250
LARTOU Sell 40
GVKPOW Buy 1000
ENGIND Sell 101
RELCOM Sell 200
TATPOW Sell 10
RELIND Sell 10
TISCO Sell 350
RELCOM Sell 100
SESGOA Buy 250
TATPOW Sell 20
INFTEC Sell 40
RELPOW Buy 200
RELCOM Sell 100
TATPOW Buy 25
BHATE Buy 35
ENGIND Buy 101
TATPOW Buy 25
RELCOM Buy 100


Cell D1-- If I input TATPOW then Cell D2 should give me value 50

Hi,
in D2 enter =sumproduct((a1:a100=d1)*(b1:b100="buy")*c1:c100)
Adapt ranges to your needs but they must be the same length
Full column ranges ( like B:B) are prohibited in versions earlier tha
xl200

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Jarek Kujawa]

one way:

=SUMPRODUCT(($A$1:$A$100=D1)*($B$1:$B$100="buy")*($C$1:$C$100))

another:

=SUM(IF($A$1:$A$100=D1)*($B$1:$B$100="buy"),$C$1:$C$100,))
CTRL+SHIFT+ENTER this formula (instead of simply using ENTER) as it s
an array-formula

HIH

 

[ABRAR]

Thanks The 1st formula works
In 2nd formula one bracket is missing actually it should be
=SUM(IF(($A$1:$A$100=D1)*($B$1:$B$100="buy"),$C$1:$C$100,))

 

[Jarek Kujawa]

thks a ton
;-)

Thanks The 1st formula works
In 2nd formula one bracket is missing actually it should be
=SUM(IF(($A$1:$A$100=D1)*($B$1:$B$100="buy"),$C$1:$C$100,))





- Pokaż cytowany tekst -