Sumif function with two criterias
 

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

Sumif function with two criterias
 

ABRAR;274312 Wrote:
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

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 than
xl2007


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=76481

Sumif function with two criterias
 

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


On 18 Mar, 08:04, ABRAR wrote:
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

Sumif function with two criterias
 

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" wrote:

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


On 18 Mar, 08:04, ABRAR wrote:
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

Sumif function with two criterias
 

thks a ton
;-)


On 18 Mar, 10:44, ABRAR wrote:
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" wrote:
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

On 18 Mar, 08:04, ABRAR wrote:
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- Ukryj cytowany tekst -

- Pokaż cytowany tekst -