Subtotal

[afs24]

I'm using the subtotal function under data to total a buch of columns. However, I want the name to show in the same row as the subtotal column. Below is an example:


Company Item Name Store Units Cost
TAR 111 Jason 5 6 $4
TAR 111 Jason 13 3 $2

sub tot. line - 18 9 $6

I want the name to show up on the subtotal line as well? The name will always be the same because I've sorted by Item and getting subtotals at change in item. Thanks.

 

[DRB]

You mean the name Jason or the word "Name" as in your title?

Either way you could concatenate a string with a formula.


="Jason " & SUBTOTAL(9,D2:D3)

Result: Jason 18

 

[Yogi Anand]

Hi afs24:

I wonder whether you are refering to the fact that when you use the DATA|Subtotlas, and in the dialog box that pops up, you need to select NAME from the list for DropDown At_each_change_in -- as in the following illustration ...

Book4
	A	B	C	D	E	F
1	Company	Item	Name	Store	Units	Cost
2	TAR	111	Jason	5	6	$4
3	TAR	111	Jason	13	3	$2
4			JasonTotal	18	9	$6
5	SAR	11	Ken	4	5	$3
6	SAR	11	Ken	12	2	$1
7			KenTotal	16	7	$4
8			GrandTotal	34	16	$10
Sheet8

Just A Thought!

 

[bosco_yip]

Q&A18.xls
	A	B	C	D	E	F
1	Company	Itemname	Store	Units	Cost
2	TAR111	Jason	5.00	6.00	$4.00
3	TAR112	Tom	13.00	3.00	$2.00
4	TAR111	Jason	13.00	3.00	$2.00
5	TAR112	Tom	7.00	2.00	$7.00
6	TAR112	Tom	12.00	5.00	$10.00
7
8		GrandTotal:	50.00	19.00	$25.00
9
Sheet1 (2)

In Cell B8 enter :

=IF(COUNTA(A2:A7)>SUBTOTAL(3,A2:A7),INDEX(B2:B7,MATCH(SUBTOTAL(4,E2:E7),E2:E7,0))&" total :","Grand Total :")

After sorted by AutoFilter in B1, B8 will return "Jason Totol :" or "Tom Total :"

HTH

Regards

 

[afs24]

This is what I need it to look like...I was thinking I can run some sort of macro to get the subtotals and populate the item # and the name in the subtotal row? thank you.

Company Item Name Store Units Cost
TAR 111 Jason L 3 $6
TAR 111 Jason L 4 $7
subtot. 111 Jason L 7 $13.00
TAR 112 Ken J 16 $11
TAR 112 Ken J 20 $7
sutot. 112 Ken J 36 $18.00

 

[Yogi Anand]

Hi afs24:

Looks like closest you can come with what you want is by using DATA | SUBTOTAL at Each_Change_In_Name as I suggested earlier. See if that is acceptable for you.

 

[afs24]

The thing is I need to see the name and the item number. If I do at change in name the item number is now not showing?

 

[GorD]

If you use Yogis method you can then collapse the data by using the numbers in the left margin. Then use an offset formula to pull the item no from the cell above. Paste this to all the subtotal lines, before showing the whole data again. By using this method you can get as many "titles" as you require

 

[afs24]

What would the code look like to do something like this because there are a lot of names that I would have to copy and paste the formula for? thanks

 

[Yogi Anand]

Hi afs24:

Let us see this is what we have so far ...

y040220h1a.xls
	A	B	C	D	E	F
1	Company	Item	Name	Store	Units	Cost
2	TAR	111	Jason	5	6	$4
3	TAR	111	Jason	13	3	$2
4			JasonTotal	18	9	$6
5	SAR	11	Ken	4	5	$3
6	SAR	11	Ken	12	2	$1
7			KenTotal	16	7	$4
8			GrandTotal	34	16	$10
Sheet8

subtotlas at Each_Change_in_Name added to Store, Units, and Cost fields.

Now we will like to identify the Item identified with each Name. Let us do this using the following steps ...

1. in the illustration above, select all the related records in column B i.e. B2:B7

2. do EDIT|Goto|Special|Blanks

3. goto formula bar, key-in =

4. click on cell B3, and then do CTR+ENTER

and the item numbers for each of the corresponding names will be added as shown with aqua colored cells in my illustration.

y040220h1a.xls
	A	B	C	D	E	F
1	Company	Item	Name	Store	Units	Cost
2	TAR	111	Jason	5	6	$4
3	TAR	111	Jason	13	3	$2
4		111	JasonTotal	18	9	$6
5	SAR	11	Ken	4	5	$3
6	SAR	11	Ken	12	2	$1
7		11	KenTotal	16	7	$4
8			GrandTotal	34	16	$10
Sheet8 (2)

5. you may optionally convert those from formulas to values.

Is this what you are looking for?