Insert Subtotals Command

**nicolachen** · 10-21-2007, 02:30 AM

Hi I ran into a problem, it state "Use the subtotals command to sum the totals for each sales person) *Hint: convert the list to a normal range before calculating the subtotals.

I highlight Sales person and click Date but subtotals key is not showing up. dunno why? can some one help, i have attach the file its in the Subtotals worksheet. thank you

**davesexcel** · 10-21-2007, 05:40 AM

Subtotal adds only what you have filtered
example
=SUBTOTAL(9,J5:J79)

enter
=subtotal(
then hit the function wizard the fx beside the formula bar, then click on help with this function to see what you can do with subtotal function

**nicolachen** · 10-21-2007, 11:18 AM

Hi Dave I try your fomula but the grand total doesn't add up right
The grand total suppose to be $8003.77 but i got $6290.82 dunno what i did wrong? can you check for me. i have attach the file thanks

**VBA Noob** · 10-21-2007, 11:34 AM

Use excel built in subtotal to avoid human errors. e.g

your formula should being J82 not J81

Please Login or Register  to view this content.

http://www.bettersolutions.com/excel...A823711331.htm

VBA Noob

**corinereyes** · 10-21-2007, 11:47 AM

Hi nicolachen,

Where did you get your total "8003.77", the total (correct me if im wrong) is "7707.96" in your spreadsheet when i used grouping for the subtotals.

See the attached file, then select J83 of sheet named Subtotal.

**nicolachen** · 10-21-2007, 12:15 PM

Originally Posted by corinereyes

Hi nicolachen,

Where did you get your total "8003.77", the total (correct me if im wrong) is "7707.96" in your spreadsheet when i used grouping for the subtotals.

See the attached file, then select J83 of sheet named Subtotal.

Hi Corinereyes
I look over the sheet there's missing data i just correct it, I got my PivotTable Grand Total $8003.77 but I still can't get the subtotal Grand Total. I try your formula but i got ##### what i did wrong???? please help....

VBA Noob (I try your formula i got $2535.35 wasn't even close to the Total show in the book $8003.77 ) u sure its the right formula?

**VBA Noob** · 10-21-2007, 12:20 PM

Your calc of $8003.77 is wrong. Both corinereyes and my total is $7,707.96 as per my attachment. The formula was your formula showing you that you had referenced a cell wrong.

As I said use excel built in function to avoid these mistakes as per my example

VBA Noob

**nicolachen** · 10-21-2007, 12:24 PM

the attach file above is the wrong one, here's the right one

**corinereyes** · 10-21-2007, 12:39 PM

Hi nicolachen,

Use the grouping in excel or create an outline in excel 2003 to avoid mistake as what VBA Noob suggested.

Attached is the file.

**nicolachen** · 10-21-2007, 12:49 PM

Originally Posted by corinereyes

Hi nicolachen,

Use the grouping in excel or create an outline in excel 2003 to avoid mistake as what VBA Noob suggested.

Attached is the file.

Hi Corine and VBA Noob
I did try to grouping it before but wouldn't let me see pic. i can't click it.

**VBA Noob** · 10-21-2007, 01:07 PM

Could be because you have a cell still selected by double clicking it. Try pressing return

VBA Noob

**corinereyes** · 10-21-2007, 01:18 PM

Im using xl 2007.

Try the following if VBA Noob's suggestion didn't worked.

1. Copy the data into another sheet and Paste Special Values (to clear all formats done.)

2. Select the whole data, then go to Subtotal.
In the subtotal ->

At each change in: Select "Sales person"
Use function: Sum
Add subtotal to: Select " Total"

mark or check the Summary below data.

3. Click ok

**nicolachen** · 10-21-2007, 01:18 PM

it state "The Command could not be completed by using the range specified. Select a sing cell with the range and try the command again"

but when i click the single cell the subtotal is not available like in that picture wouldnt let me click.

its not the freeze panel, i try to unfreeze it and still doesnt work. dunno where the problem. please check what i did wrong i had the file attach it. thanks

**corinereyes** · 10-21-2007, 01:24 PM

Im using xl 2007.

Try the following if VBA Noob's suggestion didn't worked.

1. Copy the data into another sheet and Paste Special Values (to clear all formats done.)

2. Select the whole data, then go to Subtotal.
In the subtotal ->

At each change in: Select "Sales person"
Use function: Sum
Add subtotal to: Select " Total"

mark or check the Summary below data.

3. Click ok

or you can try to pratice with the xl file i attached and follow the procedure above.

**nicolachen** · 10-21-2007, 02:05 PM

Thank You so much both of you, finally it works out and get my total right thank you a bunch

**corinereyes** · 10-21-2007, 02:26 PM

I'am glad we helped you.

Insert Subtotals Command

LinkBack

Thread Tools

Rate This Thread

Display

Insert Subtotals Command

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions