Subtotal

robbyn · OOo Advocate Joined: 29 Mar 2004 Posts: 308

Is it possible to have the subtotals in the new column? If not what is the best way to achieve it?
_________________
Robin

When you love a delicate rose remember she has thorns and a root. In time the petals will fall away.

jrkrideau · Posted: Sun Apr 30, 2006 11:02 am Post subject: Re: Subtotal

robbyn · OOo Advocate Joined: 29 Mar 2004 Posts: 308

I was referring to the data subtotalling function and also the subtotalling within the data pilot. I would like the results to be in a different column from the figures being added. eg:

Cats 110
Dogs 200
Subtotal 310
_________________
Robin

When you love a delicate rose remember she has thorns and a root. In time the petals will fall away.

BookLady · Super User Joined: 03 Nov 2004 Posts: 727

Try putting an IF(A1="";K1;"") formula in the first blank column. where K1 is the column with the subtotals in it.

Hope someone can point out a more elegant solution.
_________________
One of these days I'm gonna stop procrastinating.

David · Posted: Mon May 01, 2006 4:02 pm Post subject:

Perhaps someone can help me here, since I'm not an accountant. What is wrong in the example given with just using a SUM or SUMIF function wherever you need it [the column of your choice]?

David.

robbyn · OOo Advocate Joined: 29 Mar 2004 Posts: 308

The reason why sumif does not help is because I would want to use all the individual subtotals.
_________________
Robin

When you love a delicate rose remember she has thorns and a root. In time the petals will fall away.

BookLady · Super User Joined: 03 Nov 2004 Posts: 727

Right. The idea is to be able to pull all the subtotals out and make a new sheet of those subtotals from several different sheets. In addition they need to be inverted on the new sheet so that the subject headings go across the top of the page and all the subtotals line up under the proper heading. But that is a different problem, first it is neccessary to get the subtotals out of the column with other entries in it. Using the if formula gets a new column which can then be manipulated.
_________________
One of these days I'm gonna stop procrastinating.

robbyn · OOo Advocate Joined: 29 Mar 2004 Posts: 308

I had a look again at what I am trying to do and thank you all for your support.

As a result of your comments and concentrating just on the datapilot subtotals, I have worked out a solution:

=IF(ISERROR(SEARCH("result";C14;1));0;E14)

It is a nuisance having to add the iserror but it seems that search will not give a 0 if nothing is found. Instead it gives an error message.

The above formula applies to a pilot with groups and one subgroup, but the principle will work on any subtotal by a table pilot as each subtotal has the automatic description of "result" in it.

If there is a better way of extracting the subtotals to a new column please advise.
_________________
Robin

When you love a delicate rose remember she has thorns and a root. In time the petals will fall away.

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

You could instead use IF(ISNUMBER....)

robbyn · OOo Advocate Joined: 29 Mar 2004 Posts: 308

Better idea. Thanks.

Robin