Solved

Using subtotal to sum every other cell

Posted on 2012-03-20
15
231 Views
Last Modified: 2012-03-26
Title says it all
0
Comment
Question by:BigWill5112
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 3

Expert Comment

by:DaFranker
ID: 37743561
It actually doesn't.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37743578
Why are you using Subtotal? Do you actually want to sum every other visible cell, or just every other cell?
0
 

Author Comment

by:BigWill5112
ID: 37743590
LOL
See attached
Book1.xls
0
 

Author Comment

by:BigWill5112
ID: 37743597
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?
0
 
LVL 3

Expert Comment

by:DaFranker
ID: 37743609
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.
0
 

Author Comment

by:BigWill5112
ID: 37743633
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37743647
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 3

Expert Comment

by:DaFranker
ID: 37743659
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.
0
 

Author Comment

by:BigWill5112
ID: 37743715
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?
0
 
LVL 3

Expert Comment

by:DaFranker
ID: 37743816
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.
0
 

Author Comment

by:BigWill5112
ID: 37743844
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.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 300 total points
ID: 37744361
In D8:
=SUM(IF($B$2:$B$5=C8,IF(SUBTOTAL(2,OFFSET($C$2:$C$5,ROW($C$2:$C$5)-MIN(ROW($C$2:$C$5)),0,1)),$C$2:$C$5,0)))

array-entered using Ctrl+Shift+Enter and then copied down.
Book1-3.xls
0
 
LVL 3

Assisted Solution

by:DaFranker
DaFranker earned 200 total points
ID: 37744380
Hmm. Well, let me see if I understand the problem properly:

You have a large table arranged like the example workbook, and you want to let your users use the AutoFilter to select their own name, and see somewhere on the right or in some other location two different totals: One total for "projected" values, and another total for "actual" values, in rows that have their name on it. I'm assuming each user can have multiple pairs of values for various products, projects, or whatever.

If that is the case, my personal first solution would be to script a small UDF that returns the currently selected filter of the first column, and put that UDF as the condition for column 1 in a DSUM() criteria table, with "actual values" being another criteria. This would assume that each of those rows are properly labelled as such.

Fortunately, Ozgrid already has code for a UDF that does exactly this, which you can find here. Some minor reworking is needed for it to be useful in a DSUM call though, so here's a modified version:

Function AutoFilter_Criteria1(Header As Range) As String
    Dim strCri1 As String
    Application.Volatile
    With Header.Parent.AutoFilter
        With .Filters(Header.Column - .Range.Column + 1)
            If Not .On Then Exit Function
            strCri1 = .Criteria1
        End With
    End With
    If Left(strCri1, 1) = "=" Then strCri1 = Mid(strCri1, 2, Len(strCri1) - 1)
    AutoFilter_Criteria1 = strCri1
End Function

Open in new window


And here's your example workbook back modified with the above changes, in case you want to see if it's what you're looking for: Book1-1-.xls
0
 
LVL 3

Expert Comment

by:DaFranker
ID: 37744391
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
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 37744404
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now