Subtotal based on filtered list with more than one condition

I have this table,

A B C
----------------------
buy 50 apple
buy 130 apple
buy 20 apple
sold 50 apple
sold 25 apple
buy 20 cherry
buy 50 pear
sold 2 cherry
sold 5 cherry

and I filter it by choosing a fruit, for exemple "apple". I want to know how I can get the subtotals ("buy" and "sold") for the chosen fruit and put them on two cells.

the result shoul be like this:

Total "buy" 200
Total "sold" 75

Best Regards
Elio

I'm not sure that you really need Visual Basic for this. I'd use a sumif.

I took your data and in column D I added the formula:
=+A2&C2 into each cell

Then, in columns F, G, & H I created a little summary table:
Column F has apple, cherry, pear
in row 1 column G has buy and column H has sold
in each of the 6 cells in the table, there is this formula:
=SUMIF($D:$D,G$1&$F2,$B:$B)

Here are the results:

buy sold
apple 200 75
cherry 20 7
pear 50 0


- Pete

Your idea is great. I had to make a few changes, but now my problem is gone. Thanks for your help.

Elio

Assumptions:

A15 contains the label "buy"
A16 contains the label "sold"

Formula:

B15, copied to B16:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$2:$B$10,ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10)),0,1)),--($A$2:$A$10=A15))

Hope this helps!

Originally Posted by efernandes67

I have this table,

A B C
----------------------
buy 50 apple
buy 130 apple
buy 20 apple
sold 50 apple
sold 25 apple
buy 20 cherry
buy 50 pear
sold 2 cherry
sold 5 cherry

and I filter it by choosing a fruit, for exemple "apple". I want to know how I can get the subtotals ("buy" and "sold") for the chosen fruit and put them on two cells.

the result shoul be like this:

Total "buy" 200
Total "sold" 75

Best Regards
Elio

Wow Thanks Domenic

I have the same problem and your formula works perfectly
but can you help explain how this formula works?

Best regards
pablo