Link to home
Start Free TrialLog in
Avatar of BigWill5112
BigWill5112

asked on

Using subtotal to sum every other cell

Title says it all
Avatar of Frank White
Frank White
Flag of Canada image

It actually doesn't.
Avatar of Rory Archibald
Why are you using Subtotal? Do you actually want to sum every other visible cell, or just every other cell?
Avatar of BigWill5112
BigWill5112

ASKER

LOL
See attached
Book1.xls
rorya,
yes - i want to subtotal every otehr visible cell.  Manager are filtering based on their name and they have a forecast row and actual row for alot of payments.  I want to the totals to be by manager.

Is there a way to create a forumla based on a filter value?
On top of rorya's questions:

Does the subtotal need to be volatile, i.e. update itself automatically every time cells are changed or values added or rows shifted and so on? Does it need to be done in a hidden fashion because this is for unknown end-users and needs to be user-friendly? Is this something that should happen when you press a button instead? Maybe a one-shot macro?

You're leaving out too much context information for us to provide an effective solution. There is no catch-all way to have subtotal just sum every other visible cell, and if you don't want any complexity, the simplest way is to merely manually hide one out of two rows before you do the subtotal.
sounds like a macro then...unless there is a way to write a formula based on a filter value...then i might be able to use a CSE formula.  thoughts?
It sounds to me like you want a pivot table really.

Is there anything that identifies whether a row is forecast or actual (e.g. data in another column) that the formula could use? If so, it can be done with SUBTOTAL and OFFSET.

PS a sample workbook showing the data layout would really help.
Now that I've taken a look at the attached example workbook (my posts seem to be delayed somehow? Or maybe I'm just typing that slow =\)...

It seems to me like DSUM() might provide the ideal solution to this problem. It can sum the values in a specified column of a particular range based on criteria put elsewhere, and makes adding or removing criteria like specific text and text matching another cell much easier.
hey hey dsum() looks like a beauty....my only problem is how do I get the criteria based on a filtered cell...know what I mean?
Your DSUM() criteria table could probably have a formula =(Someothercellorformula) under the criteria headers so that the comparison itself is dependent on the filtered data.

I don't have any appropriate datasets on hand to test this though, and I rarely work with Database functions anyway so my experience here is somewhat limited. I tend to just whip up some VBA code whenever I need to do things as complex as this since VBA feels more "natural" to me, unless I critically need to optimize performance.
can you whip up some vba code...what would you do...just change the formula in the cells based on a worksheet change of the filter.  am i on the right rack...I could do it but it would take about a day for me.
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Heh. Looks like rorya beat me to it, with exactly the kind of formula that makes me not want to use Excel formulas and stick to VBA! /headache-trying-to-read-that-formula
Not for points but you could simplify Rory's version a little by using SUBTOTAL(9 and possibly also use SUMPRODUCT to avoid array entry, i.e.

=SUMPRODUCT(($B$2:$B$5=C8)*SUBTOTAL(9,OFFSET($C$2:$C$5,ROW($C$2:$C$5)-MIN(ROW($C$2:$C$5)),0,1)))

regards, barry