BigWill5112
asked on
Using subtotal to sum every other cell
Title says it all
It actually doesn't.
Why are you using Subtotal? Do you actually want to sum every other visible cell, or just every other cell?
ASKER
ASKER
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?
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.
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.
ASKER
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.
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.
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.
ASKER
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.
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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-t hat-formul a
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,R OW($C$2:$C $5)-MIN(RO W($C$2:$C$ 5)),0,1)))
regards, barry
=SUMPRODUCT(($B$2:$B$5=C8)
regards, barry