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.

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]store[/TD]
[TD="width: 64"]item[/TD]
[TD="width: 64"]price[/TD]
[/TR]
[TR]
[TD]linda[/TD]
[TD]east[/TD]
[TD]tv[/TD]
[TD="align: right"]662[/TD]
[/TR]
[TR]
[TD]jack[/TD]
[TD]east[/TD]
[TD]dryer[/TD]
[TD="align: right"]444[/TD]
[/TR]
[TR]
[TD]alex[/TD]
[TD]east[/TD]
[TD]computer[/TD]
[TD="align: right"]723[/TD]
[/TR]
[TR]
[TD]allen[/TD]
[TD]east[/TD]
[TD]tv[/TD]
[TD="align: right"]356[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]east[/TD]
[TD]dryer[/TD]
[TD="align: right"]792[/TD]
[/TR]
[TR]
[TD]sam[/TD]
[TD]north[/TD]
[TD]washer[/TD]
[TD="align: right"]452[/TD]
[/TR]
[TR]
[TD]adam[/TD]
[TD]north[/TD]
[TD]tv[/TD]
[TD="align: right"]998[/TD]
[/TR]
[TR]
[TD]david[/TD]
[TD]north[/TD]
[TD]dryer[/TD]
[TD="align: right"]307[/TD]
[/TR]
[TR]
[TD]eric[/TD]
[TD]north[/TD]
[TD]computer[/TD]
[TD="align: right"]464[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]south[/TD]
[TD]dryer[/TD]
[TD="align: right"]410[/TD]
[/TR]
[TR]
[TD]tim[/TD]
[TD]south[/TD]
[TD]computer[/TD]
[TD="align: right"]890[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD]south[/TD]
[TD]washer[/TD]
[TD="align: right"]899[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]south[/TD]
[TD]tv[/TD]
[TD="align: right"]931[/TD]
[/TR]
[TR]
[TD]liz[/TD]
[TD]south[/TD]
[TD]dryer[/TD]
[TD="align: right"]739[/TD]
[/TR]
[TR]
[TD]dana[/TD]
[TD]west[/TD]
[TD]computer[/TD]
[TD="align: right"]459[/TD]
[/TR]
[TR]
[TD]anna[/TD]
[TD]west[/TD]
[TD]tv[/TD]
[TD="align: right"]967[/TD]
[/TR]
[TR]
[TD]kim[/TD]
[TD]west[/TD]
[TD]dryer[/TD]
[TD="align: right"]931[/TD]
[/TR]
[TR]
[TD]louis[/TD]
[TD]west[/TD]
[TD]computer[/TD]
[TD="align: right"]481[/TD]
[/TR]
[TR]
[TD]jeff[/TD]
[TD]west[/TD]
[TD]washer[/TD]
[TD="align: right"]737[/TD]
[/TR]
</tbody>[/TABLE]

 

[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