how to sum a column if criteria column # 0

[roypabitra]

Respected Sir,


I have two three column in a data sheet like the following


TBA STAX TOTAL

100 2 103

200 0 200

300 10 310

400 0 400

100 5 105


Now sir i want to sum of TBA on which Stax not equal to Zero.


Please Help me for the above

 

[Faseeh]

Hi roypabitra,


Assuming your data in cell A1:C6, you can try:

=SUMPRODUCT(($B$2:$B$6<>0)*(A2:A6))

Regards,

 

[roypabitra]

many many Thanks

 

[anand_babu]

Hi Roypabitra,


One more approach.


=SUM(IF(B2:B6>0,A2:A6,0))


Note: This is an array formula. Need to press ctrl+shift+enter after pasting this in a cell.


Thanks

 

[shrivallabha]

You can also use:

=SUMIF(B1:B6,">0",A1:A6)


which is a regular formula.

 

[roypabitra]

I have VBA code on which I can find a exact word and pest it another sheet but I want to search and pest not exact word like the following


pabitra 100

rupa and pabitra 200

pabitra and sampa 300

rupa and sampa 400

sampa and sameer 500

sampa 600

rupa 700


now I want to copy data on which included pabitra but my VBA search and pest exeact word on which Only pabitra


Please help me

 

[shrivallabha]

Hi pabitra,


It is not in continuation with your original request. Please start a new topic.