Problem with nested subtotals, placing secondary subtotals BELOW .
I some but not all spreadsheets, my nested subtotal (slsperson) is placed
below the primary subtotal (branch). It has only happened to me since
upgrading to Excel 2003.
I sort by Branch, slsperson, customer. Then Primary subtotal is branch
(checks on replace current subtotals and summary below) and all is fine. Then
when I do subtotal on Slsperson it will place (above items unchecked) the
slsperson subtotal BELOW the branch. And also the levels (1,2,3) are not
Does anyone have a suggestion for me?
Multilevel subtotals are in the wrong position in Excel...Duplicates and subtotals
Each week we receive a file from our payroll system,
approx 6000 rows. Contains General Ledger data by
employee and GL account with amounts. We use this file to
post to our GL system. I'd like to find a way in Excel
to subtotal by row containing unique data.
Emp Co Accnt date amount
1234 10 4000 10/17/03 100.00
1234 10 4000 10/17/03 5.00
I would like to see, instead of two lines of detail,
1234 10 4000 10/17/03 105.00
Anyone had the opportunity to do this?
Thanks in advance
I'd insert a new column A and use a helper formula:
(as...Applying formulas only to the subtotals of a data list
I have a large data list with subtotals (in fact with nested subtotals). I
have collapsed the list so that only the subtotals are showing. Next I set up
a few columns to the right of the data list with the intent of applying
certain calculations, again ONLY to the subtotals.
Here lieth the problem: when I set up the formulas for the first subtotal
and attempt to simply drag it down, Excel obviously assumes that the copied
formula applies to the next row of the full-blown, uncollapsed data list. Is
there any way to accomplish the task without having to manually create a
separate table ...Division by zero in automated subtotals
What is the easiest way to division by zero errors that occur when using
automated subtotals to display averages?
Look for a count of numbers to be bigger than 0.
Neil Berkowitz wrote:
> What is the easiest way to division by zero errors that occur when using
> automated subtotals to display averages?
I would like to create a macro (or vba function) that subtotals by user
then by type. I have two fields that I would like to total, amount and
I would like the macro to sort and subtotal on type and then provide a grand
I have three different types (P, C & R).
I tried using the sort and subtotal functions in excel (2003) but have not
success. Can someone please point me in the right direction?
Since you provide little info, If I understand you don't need to subtotal.
Try this idea
=sumproduct((a2:a22="P")*(b2:...How to ignore #NA in subtotals?
I have several data tables of identical layout that are updated daily, the
first two manually and the others by preset formula that calculates from the
first two. If a zero or no data are input into cells in the first two then
the formula of the others will return #NA, this is intentional because charts
are plotted from the latter tables and #NA prevents zero's being plotted all
over it for data that has not been entered yet (zero is a valid in negative
and positive data entry only when entered).
Each table has a SUM total, but, if #NA exits in any one of the cells
When the value in column A changes, I need the amount-
subtotals for each of the values in columns B and C.
(See sample spreadsheet below
For example, for "687223007-5", I need:
For "19-038": $ 388.89
For "68-1366": 30.36
For "68-1378": 3.51
For "68-1856": 12.72
For "68-2232": 332.91
For "68-4115": 353.20
(Keep in mind that, for the value in column A, the number
of entries in columns B and C may range from 1 to ???).
A B C ...Printing what I can see on the screen
On a monthly basis, I need to generate a simple 10 line report from a
very large spreadsheet. Using the Subtotals function under the Data
menu on the toolbar, I can get the results on screen in about 30
seconds. But how can I either print this out or preferably cut and
paste into a Word or email without all of the data.
...Grand Totals without subtotals
I am preparing a number of tables, all of different sizes. I woul
like to record a command in a macro that will calculate the Grand Tota
of a specific column even though the number of rows in that column wil
vary for each table I prepare. The 'subtotals' command is perfect fo
this, except I do not require to subtotal any column at a specifi
change, only Grand Total the entire column. I have tried using
simple 'sum' forumla, however this doesn't seem to work in tables wit
a larger/smaller number of rows as it selects the exact cell I recorde
in the macro.
Does any...Refreshing Data loses subtotals
I am currently running a database query from an access db in Excel
2000. I have it set just the way I want, then I decide I'd like a
subtotal of something. I create the subtotals the way I'd like and
save the spreadsheet. Everything is great until I change the data in
the db and click refresh data, at that point I lose my subtotals.
Is there a way to save a spreadsheet and its subtotal functions so it
will retain them the next time it is refreshed?
I was looking at External Data Range Properties and there are
functions I can't understand that may hold the key... or not.
If so...Subtotals by page
Is there any way of getting excel to
generate subtotals by page? these
should update automatically when
...Pivot Table unable to Include Hidden Items In Subtotals
I have set up a pivot table that lists only the Top 10 values in a column,
but also want the subtotal for the entire column, including the hidden items.
Excel Help tells me that I can click the "Include Hidden Items in Totals"
button, but the button is not available. I followed the help instructions to
first select "Subtotal Hidden Page Items" then to unselect it and didn't get
my result in either case. Any ideas on why the button is not available?
I have a spreadsheet and have used the subtotal (Data --> Subtotals). I have
chosen to subtotal dollar amount for a particular vendor number, however the
names of the vendors does not appear then. How can I get the name of the
vendor to appear as well?
Instead of subtotals, you could use a pivot table to summarize the data.
Jon Peltier has instructions and links on his web site:
Add Vendor number and Vendor name to the row area, and put the dollar
field into the data area, where it will become Sum of Dollars.
TJAC...pivot table row subtotals
I am pretty good at pivot table but can't figure out how the have subtotals
display for my 3rd row field in under my 2nd row field - they only display at
the grand total line.
Please see the example below. I need to get subtotals for A/P and OP under
store 1 and then again under store 2.
Also My calculated field do now show up in my grand totals? why not - they
should work - the math is valid.
Div Desc Desc Ver
Store 1 4 OP
Store 1 Total
Store 2 5 OP
Store 2 Total
Move ...Help with Pivot Table subtotals?
My Pivot Table looks like
COOLANT 50/50 Total
COOLANT 60/40 Total
I'd lke to get rid of the "Coolant 50/50" and "Coolant 60/40" subtotal
lines. Double-clicking "Coolant 50/50" also makes "Gallons" go away. Is
there a setting or property that will do this?
Apologies - I tried pasting in an actual table from Word and it didn't take
But - I did find the answer: Field Settings, select None under Subtotals.
...Avereging the Subtotals in Reports
I am trying to do this in the Reports.
Lets say, in the details, there are different account numbers with
corresponding balances. These balances are totalled in the footer of that
Group (month) =Sum([balances]). Then, I need to Average all the monthly
totals in the footer of the Report. In the report footer, if i use
=Avg([balances]) , it averages ALL the details, NOT the monthly totals.
Any help? thanks.
I would create a totals query that can be added to the report's record source
query. For instance, if you want to find the Average of the Monthly Freight
values fr...Why use Subtotals 1-11 instead of the built-in functions directly?
I see the need to use 101-111 for Subtotals; e.g. I want to ignore hidden
But, I don't see why I want to use Subtotals 1-11, instead of using the
built-in functions like average, count, max etc. directly.
I do understand that if I use Data>Subtotals, I can later modify the
Subtotals function which may have 1-11.
Please enlighten me. Why would I choose Subtotals 1-11 over a more direct
method? Am I missing something obvious?
> I see the need to use 101-111 for Subtotals; e.g. I want to ignore hidden
> But, I don't ...BOLDING SUBTOTALS
Is there a way to bold subtotals. I want everything on the subtotal line to
be bolded, sometimes the report is very big and right now I have to select
each row and bold each row.
How are your Subtotals created?
Data / Subtotals feature or
Pivot table or
Are all your subtotals preceded by text such as "Subtotal".
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
It's easier to beg forgiveness than ask permi...list of subtotals!
how can i get a list of subtotals?
via135's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26725
View this thread: http://www.excelforum.com/showthread.php?threadid=499188
Hi Via 135,
This was found in excel help.
You can also look at this link
----------------------------------------------...SmartList Builder SubTotals
Our client would like to create SubTotals in his SmarLists with SmartList
Builder. I don't believe this is available. I seem to recall a 3rd party
SmartList solution which incorporated some of this Excel like functionality.
Great Plains Consultant
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
Share your knowledge. Add your favorite questions and ans...Subtotals Formatting
Is it possible to change the formatting of a set of
subtotals such that they for instance could be displayed
in red to make them stand out, without having to change
each individual one?
One way is to show the detail you wish to change, then use Edit/Go
to/Special/Visible cells. This will select all visible cells only, which you
can then format as you wish.
"Simon Topping" <firstname.lastname@example.org> wrote in message
> Is it possible to change the formatting of a set...Excel subtotals, add a sort option, and BOLD the function answers
love using the subtotal option. sometimes forget to sort the list first,
would be nice to have a sort option or reminder. Also when subtotals are
applied, the heading or titles for the subtotal is BOLD, the entire line that
was added should be bold, or have options to automatically add borders
if you'ld like to email microsoft with your comments the email address is
however, if you're asking how can the whole line go bold when using
subtotals - conditional formatting is often used to achieve this. Post
back if you'ld like more details
JulieD...Beginner question for months and subtotals
I am new to Excel (have limited exp with Word vba). I would like t
enter a list of data where column 1 shows a cost item on the lef
followed by the 12 months of the year. Row 1 will be for net, gross
tax, date inputs. Every month should have a subtotal. The plan is t
make a master sheet which I can use again for the next year.
I'm able to do this manually by creating an extra row in th
appropriate month and then at the end of the month make my subtotal
using the autosum icon, etc.
The problem is that I never know how many costs are coming for th
month and so for the master sheet I don...Trouble with subtotals
I have data exported from crystal reports 9 and want to subtotal. I subtotal
and it gives me the subtotals but not the groupings. Any ideas?
Question answered on a post from 10/22/05. Tools\view\check outline symbols
> I have data exported from crystal reports 9 and want to subtotal. I subtotal
> and it gives me the subtotals but not the groupings. Any ideas?
...Subtotals by Year?
Hi - here's the scenario:
Column A is a person's name.
Column B is a date, e.g., 4/1/1979.
Column C is an amount of money.
I'd like to subtotal this spreadsheet by date - but not by individual
day, rather by year - i.e. I would like one subtotal for 1979, another
for 1980, etc.
Is this achievable?
Thanks very much.
Microsoft MVP Excel
"Guest3731" <email@example.com> wrote in message