Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Using subtotal to sum every other cell

Title says it all
0
BigWill5112
Asked:
BigWill5112
  • 6
  • 5
  • 3
  • +1
2 Solutions
 
Frank WhiteCommented:
It actually doesn't.
0
 
Rory ArchibaldCommented:
Why are you using Subtotal? Do you actually want to sum every other visible cell, or just every other cell?
0
 
BigWill5112Author Commented:
LOL
See attached
Book1.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BigWill5112Author Commented:
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
 
Frank WhiteCommented:
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
 
BigWill5112Author Commented:
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
 
Rory ArchibaldCommented:
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
 
Frank WhiteCommented:
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
 
BigWill5112Author Commented:
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
 
Frank WhiteCommented:
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
 
BigWill5112Author Commented:
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
 
Rory ArchibaldCommented:
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
 
Frank WhiteCommented:
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
 
Frank WhiteCommented:
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
 
barry houdiniCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now