Subtotal with a criteria

[Sachin2k6]

Hi All

I have a filtered range like this :


RowNo. ColumnA ColumnB
1 Gold 35
3 Silver 25
8 Silver 28
10 Gold 45

Range can be extended up to 10000 rows. I want the subtotal of the visible cells of the column B when the corresponding cell in columnA contains 'Gold'. How can i get this??

Thanks in advance.

 

[Robert Mika]

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$2:B$10000,ROW($A$2:$A$10000)-MIN(ROW($A$2:$A$10000)),,1)),--($A$2:$A$10000="gold"))

 

[Sachin2k6]

Many thanks robert.

 

[Robert Mika]

Many thanks robert.

You are welcome.

 

[Sachin2k6]

just one more thing ,what is the meaning of '--' in the end portion. If i omit these signs then formula results 0. Why???

 

[Aladin Akyurek]

just one more thing ,what is the meaning of '--' in the end portion. If i omit these signs then formula results 0. Why???

http://www.mrexcel.com/forum/excel-questions/202204-find-last-cell-data-formular.html

 

[Sachin2k6]

thanks aladin