subtotal() function doing Data-->subtotal job

[lezawang]

Hi
I have a table like the one below. I sorted "Store" column. I want to find the subtotal of east store or subtotal of north store or north store etc. using subtotal() function.

I know after sorting, I can go to Data tab-->subtotal, that is an option
another option maybe I can use SumIf() function. Another option I can use pivot table.

But I wonder, can I use subtotal() function to do exactly what Data-->Subtotal does? Thank you very much.

name	store	item	price
linda	east	tv	662
jack	east	dryer	444
alex	east	computer	723
allen	east	tv	356
mary	east	dryer	792
sam	north	washer	452
adam	north	tv	998
david	north	dryer	307
eric	north	computer	464
jim	south	dryer	410
tim	south	computer	890
tom	south	washer	899
sue	south	tv	931
liz	south	dryer	739
dana	west	computer	459
anna	west	tv	967
kim	west	dryer	931
louis	west	computer	481
jeff	west	washer	737

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Gerald Higgins]

I don't think you can easily use SUBTOTAL() to find, for example, the subtotal for SOUTH, unless you are using Data Filters to select SOUTH entries.

I think in this situation I would use SUMIF (or SUMIFS), or PIVOT TABLE.

 

[DanteAmor]

You can use subtotal() as a complement to sumproduct, that way it also helps you when you have a filter, eg by item: computer, and you want to get the subtotal of "west", tyr this:

Excel Workbook
	A	B	C	D	E	F	G
1	name	store	item	price			940
4	alex	east	computer	723
10	eric	north	computer	464
12	tim	south	computer	890
16	dana	west	computer	459
19	louis	west	computer	481
Hoja3