Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I happily pick values out of a list with a SUMIF function.

    How can I exclude SUBTOTALS, which are peppered throughout the list ?

    I seem to need a SUBTOTALIF function, but that doesn't exist :-(

    All the cells to be added contain formulas, so I cannot use that as an additional criterion, but could I perhaps parse what the first letters of formula are, for example ?

    Thanks

    Martin

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Can you post an example?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Attached.

    You can see that the totals in Column B for the categories in Column A are overstated due to the inclusion of the subtotals (in yellow) in Column F.

    The total in F18 correctly does not include these subtotals.

    I'd prefer to use a worksheet function (rather than VBA) for this task, if I can.
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Ok, I've sorted it !

    There's a column way to the left which is always blank when there's a SUBTOTAL, so I've used a SUMPRODUCT to test for this and exclude any values in that row.

    Its still a pity there's no SUBTOTALIF function, corresponding to SUMIF. Ah well . . .

    Martin

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    It is unclear - what is the purpose of the Yellow subtotals in Col F? They don't total the "Two"'s or the "Four"'s in the "Raw data".
    If those subtotals are really needed, change the labels in E5 from "Two" to "Subtotal Two" and E12 from "Four" to "Subtotal Four", then your SUMIF will work.

    Depending on what the rest of the data looks like, this data may be a good candidate for Pivot Tables.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by MartinM View Post
    Its still a pity there's no SUBTOTALIF function, corresponding to SUMIF. Ah well . . .
    If Mr. Gates & Co won't provide, we do it ourselves. Here is a UDF solution.

    [attachment=90852:Example_r1.xls]
    Attached Files Attached Files
    Regards
    Don

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Tim,

    The raw data (the column of mixed data and subtotals) is in fact grouped, and only the subtotals show. The group can be is expanded in case the supporting detail is needed. I didn't really want to change the label as it is itself an Excel name, is used extensively elsewhere, and it would confuse the users if it had another one !

    But your suggestion is a good one in principle and I've had in fact used something similar as described in my previous post.

    And I will look into using a Pivot Table, which would make the whole analysis issue simpler I think

    Now to take a look at Don's UDF . . .

    Martin

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Don,

    Thank you for this worked example. I had not yet tackled UDFs and this is a perfect way to draw me in !

    The Workbook I am tidying up is vast, and poorly structured - but very important. I had developed a number of techniques to make it more logical, more obviously self-documenting and hence more maintainable in future. A logical naming scheme for a whole bunch of cells and ranges was a necessary start, and the SUBOTOTALIF UDF will be a great help - I've successfully used it in the area I indicated in the Example I posted and it will be similarly useful in many other places.

    I suppose the code is very similar to the existing SUBTOTAL function - maybe MS will adopt your version, but don't count on any royalties !

    Martin

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Don,

    A trivial question, but one that is puzzling me.

    The UDF works prefectly: when I type it, Excel turns it into SUbtotalIF whereas the Module is clearly labelled SubTotalIF. How come the capitalisation is changing ?

    This is not a functional issue, it merely offends my desire for tidiness !

    Martin

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by MartinM View Post
    Don,

    A trivial question, but one that is puzzling me.
    Martin

    The same question has baffled me. I suspect that it is a consequence of my entering the formula (poorly typed), prior to generating the function. However I am not certain of my sequence of actions in developing the solution.
    Regards
    Don

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Don,

    It seems that Excel "remembers" the first way you type in the function. In my real-life worksheet I carefully typed it "as it should be" and it always returns to that.

    On to more important things, I have discovered a minor bug which I have diagnosed but cannot - as yet - sort out.

    The UDF fails if the capitalisation of the Crit is not the same as the Label it is seeking to match, but in a strange way:

    The WorksheetFunction.SumIf(...) includes the item that is wrongly capitalised, but the And rcell = Crit does not include the item, leading to an erroneous result.

    I can fix this by changing the capitalisation in the Labels I have used but wondered if there's a way to amend the And rcell = Crit test to ignore captalisation and thus make the function internally consistent ?

    Martin

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by MartinM View Post
    The UDF fails if the capitalisation of the Crit is not the same as the Label it is seeking to match, but in a strange way:
    Martin

    The following modified code will repair that.
    Code:
    Function SubTotalIf(rngRange As Range, crit As Variant, rngVal As Range)
    Dim Subt As Double
    Dim rcell As Range
    Dim vcell As Range
    Dim n As Long
    
      n = 1
      For Each rcell In rngRange
        If Left(rngVal.Cells(n).Formula, 10) = "=SUBTOTAL(" _
          And UCase(rcell) = UCase(crit) Then
          Subt = Subt + rngVal.Cells(n)
        End If
        n = n + 1
      Next rcell
      SubTotalIf = WorksheetFunction.SumIf(rngRange, crit, rngVal) - Subt
    End Function
    Regards
    Don

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Job done !

    Many thanks, Don, for the code and for the teaching

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •