# Thread: SUBTOTALIF( . . .)

1. 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. Can you post an example?

3. 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.

4. 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. 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. Originally Posted by MartinM
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]

7. 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. 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. 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. Originally Posted by MartinM
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.

11. 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. Originally Posted by MartinM
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```

13. 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
•