Subtotalling columns with different categories

[datacruncher]

I'm not sure how to describe the title best but have a large data file (aren't they all) from accounts. The headers have an auto-filter applied so I can quickly choose what areas I want to look at, screen-shot attached.

I can easily add a subtotal to the foot of the document so as I filter things off the page, the subtotal is updated. Looking at the data there are numerous different projects with separate descriptions. I'd like to be able to subtotal all of the groupings for example Commercial runs from B11:B15 and it's total is £2269.22, another example is Business Support that runs from B9:B10 and it's total is £2280.60.

Is there a formula I can put at the right hand side of the data that will update the subtotals of the 'groupings' as I change the filters?

The next question is how can I read that subtotal figure in another worksheet updating as the filtering does?

Not sure if this uses a standard formula (part 1) or is a complex amalgamation of them. Please help!

[DonkeyOte]

Why not utilise the Subtotal functionality, ie highlight your data and insert a subtotal such that on each change of Project you insert a Sum of column B.

As you alter selections in the other columns so the individual Project based subtotals should adjust accordingly.

Using adjacent formulae will IMO be very inefficient.

[DonkeyOte]

One way you could possibly do this with adjacent formulae... assuming (as before) that data is sorted by Project and running total to go in column F:

F2: =SUM($B2*SUBTOTAL(2,$B2),IF($E1=$E2,F1,0))
copied down

the last value for each project should give the running (visible) total for that project.

[datacruncher]

Thank you very much for your input. I applied the formulae and it seems to total in every cell, not at the bottom of a range which is preferable. The logic seems to just copy the amount over to a new cell via an intelligent formula..

I'm wondering if it's the count function in the subtotal, though I am grey on this function. Any ideas? I have attached a sample file with notes where I would prefer the outputs to be, hopefully that will explain it better.

[DonkeyOte]

As I mentioned earlier - the formulae/functions required to generate a Project Subtotal which would appear only on the last "visible" row of a given project would be very inefficient (ie Volatile Arrays).

The formula posted in post 3 is something of a trade-off

F2: =SUM($B2*SUBTOTAL(2,$B2),IF($E1=$E2,F1,0))

in so far as it will generate a visible cumulative total but will (as you and I have both pointed out previously) appear on each visible row which though not "ideal" regards your desired output is still significantly more efficient than the alternatives (IMO).

I guess the point I'm making is that Filtering is a Volatile action thus any Volatile functions will recalculate whenever Filtering is utilised.
If therefore you have made use of a multitude of inefficient Arrays which are themselves Volatile you are going to generate significant calculation overheads and in performance terms your file will grind to a halt pretty quickly.

as to how the earlier formula works... it takes the current amount and multiplies by whether or not the current amount is visible (ie 0 if hidden) to which it addes the prior total if the prior project is the same as the current project... this way only visible totals get added to the running total.

[DonkeyOte]

Also, I'm a tad confused by your sample given the data does not require filtering - ie you can not filter a Project without hiding all records so it's hard to provide a meaningful example based on that data alone.

If you do wish to filter... and you're adamant that you want to display totals only on last visible row I confess I would err on side of using more functions to avoid requirement of volatile arrays - ie use helpers.

Using your sample... if we assume that columns A & B contain data values that can be filtered so as to affect individual project output then I would probably opt for something like:

Please Login or Register  to view this content.

I would then use:

Please Login or Register  to view this content.

You can hide F & G ... E will only display total in last visible row for any given Project