XL97: "Subtotal If" array formula - Formula guru needed! :-) 4

[VBAjedi]

Hi all!

My workbook has invoice amounts in range "A10:A50000" (named "SSCurDocAmts&quot and an invoice Aging value (1-6) in range "B10:B50000" (named "SSAgingBuckets&quot.

I need to generate six invoice amount subtotals (one for each possible Aging value). I think I need an array formula that sums all the values from VISIBLE rows in named range "SSCurDocAmts" if the value in named range "SSAgingBuckets" is, say, 1. This is my best shot, but doesn't work:

{=SUBTOTAL(9, IF(SSAgingBuckets=1,SSCurDocAmts))}

This works, but sums all rows, not just visible ones:

{=SUM(IF(SSAgingBuckets=1,SSCurDocAmts))}

Can someone please help me?

Thanks!


VBAjedi

 

[dawnd3]

You could use the sub totals feature under the data menu.

Dawn

http://www.calliopeconsulting.net

 

[VBAjedi]

Unfortunately it isn't that simple. These two ranges are part of a data range that has over twenty columns, and I am running the Autofilter against it. What I need is six cells at the top of the spreadsheet that display the total invoice amounts for each of my six Aging "buckets" - for the filtered rows only. It's a dynamically changing summary of the filtered data.

Can someone tell how to construct the formula I'm after?

VBAjedi

 

[TonyJollans]

Hi VBAjedi,

No chance of using six extra (hidden) columns in your range which is filtered is there?

Enjoy,
Tony

 

[VBAjedi]

That would be a last-resort option. If you are thinking about filling the hidden columns with conditional formulas, then using Subtotal on each column, I don't think that would work in my case. The data area gets wiped and refreshed daily, and the amount of data (number of rows) changes as well. Besides, I'm dealing with 10000+ rows here, which would mean 60000 formulas to calculate. Ugh!

I know SOMEONE out there knows the syntax of the formula I need!

LOL




VBAjedi

 

[DaleWatson123321]

Hi VBAjedi,

Using database formulas, I believe you should be able to set up criteria for each of the 6 formulas that: a) uses the same criteria as you are currently using to display the current records, and b) add to each of these 6 criteria the values 1 - 6.

If you require help with this, email me a scaled-down copy of your file and I'll set up the criteria and formulas for you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[gnanas]

You could easily use Pivot table to find the subtotals

 

[xlbo]

gnanas - not for visible cells only you couldn't
VBAJedi - give me a mo - I'll see what I can come up with.....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[combo]

Can you use additional column? In that case user defined function:
[tt]Function CellIsVisible(r As Range) As Boolean
CellIsVisible = Not r.Rows(1).Hidden
End Function[/tt]
can be additional condition for summing up.

combo

 

[tsuji]

Hello VBAjedi,

One possible way out is to use AdvancedFilter instead of AutoFilter, and then taking advantage of the "Copy to" functionality of the former.

Once copied to some fixed place, the sum formula then reference to the reduced table containing only "visible" rows.

Instead of drop-down from AutoFilter, this scheme requires changing the "simple" criteria range simulating the criteria you set up in AutoFilter scheme. A couple of more key-strokes, maybe or maybe not.

Just an idea for a solution remaining outside of possible VBA/macro schemes, but remaining dynamic & interactive with te user of the workbook.

regards - tsuji

 

[xlbo]

Ok Jedi - got a solution but you'll need to use the force ;-)

First off, I was gonna use a UDF but because it would need to loop thru all rows, for each of the 6 formulae, it'd be a tad slow soooooooo
I've created a UDF that fetches the 1st visible value in a filtered column
This value can then be used in a standard array formula so you are in effect, using the filter to determine the criteria for the array - this still needs to loop but only once:

Function FirstEntry(headrw As Long, col As Integer)
Dim lRow As Long, stRow As Long

lRow = Cells(65536, col).End(xlUp).Row
stRow = headrw + 1
For i = stRow To lRow
If Rows(i).EntireRow.Hidden = False Then
FirstEntry = Cells(i, col).Text
Exit For
Else
End If
Next i
End Function

If you enter this in A1 for instance, with the parameter "col" = 3, it'll return the 1st visible (ie filtered) entry in col C

You could then have an array formula like:

=SUM((C6:C50000=A1)*(F6:F50000=2)*(G6:G50000))

entered with CTRL+SHIFT+ENTER

where your filter criteria is in colC, age level in F and value in G


Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Zathras]

Using array formulas results in a noticeable delay when changing filter selection. Your best solution is to combine the (very clever) UDF supplied by Geoff with the (very wise) suggestion by Dale to use database functions.

First, a slight modification to Geoff's idea. As written, the function does not get recalculated when the filter selection is changed. By modifying it to look like this:
[blue]

A1: =FirstEntry(5,3+RAND()*0)

[/color]
the formula will be recalculated when the filter selection is changed.

As Dale said, to use database functions, all you need is to set up six criteria ranges. Then you simply use 6 corresponding DSUM formulas to get the 6 ageing totals.

For example, assuming the filtering column "C" is named "SSRegion" the first critera could look like this:
[blue]

AA1: 'SSRegion
AB1: 'SSAgingBuckets
AA2: =$A$1
AB2: 1

[/color]
Then the total for bucket "1" can be obtained from this (assumes that "SSCurDocAmts" is in column "A&quot:
[blue]

H1: 'Bucket 1
H2: =DSUM($A$5:$Z$5000,1,$AA$1:$AB$2)

[/color]
To handle the more complex situation with filter selections on multiple columns (which I'm guessing is your real-life scenario), you would simply have a cell with Geoff's formula for each filterable column and the criteria ranges would be extendes to include columns for each filterable column in the database (referencing the results of the corresponding "FirstEntry" functions).

 

[VBAjedi]

There's some great ideas floating around in here!

Zathras, is it the presence of RAND() that causes the formula to recalculate when the filter is changed? I didn't quite follow that bit, and I'd like to understand it. Also, I'm currently using AutoFilter, while your approach (if I understand it correctly) uses the Advanced Filter. More power than I need for this filtering app, and a bit more bulky.

xlbo, great idea on writing a UDF - I did just that. Your code is very clever, but implementing it is still a bit bulky given what I'm after. The following is simple, and works well:

Function SumVisIf(CondRange As Range, MatchVal, ValuesRange As Range)
Dim C As Range

SUMVISIF = 0

For Each C In CondRange
   If C.Value = MatchVal Then
      If C.Rows(1).Hidden = False Then
         SUMVISIF = SUMVISIF + Cells(C.row, _
         ValuesRange.Cells(1, 1).Column).Value
      End If
   End If
Next C
End Function

Then I just call this function with:

=SUMVISIF(SSAgingBuckets, 1, SSCurDocAmts)

in whatever cell I want to put the total for Aging bucket 1. It even refreshes automatically (in a respectable 2 seconds for six of these) when the filter is changed.

Thanks for all the suggestions!


VBAjedi

 

[Zathras]

Yes, the presence of RAND() is what causes the recalculate to take place. The only other values passed to the function are constants so Excel doesn't know that recalc is necessary otherwise.

No, I wasn't suggesting advanced filters, only auto-filter as you have done.

As long as it's fast enough, doing it all in a UDF (the way you did) is certainly the easiest way to use. Although on my PC, 50,000 rows and 6 instances of the formula takes a full 8 seconds to recalculate. On the other hand 6 DSUM formulas recalculate in less than half a second. If your users complain about the speed, you might reconsider using the database approach.

Best of all, if you have any control over the input data format, would be to get the ageing amounts in 6 separate columns (instead of two columns - amount and bucket number). If you can get the data pre-configured that way, then simple SUBTOTAL formulas will give you what you want without any VBA code or database formulas.

 

[VBAjedi]

New Thought:

I think I just thought of a clever way to do this without a UDF at all! But I can't get it to work yet. . . LOL

Since the SUBTOTAL() worksheet function only looks at visible rows, why can't I use it in a criteria range for DSUM?

One criteria column would specify the aging value, the other would see if the Subtotal of that rows dollar amount was greater than 0. So my criteria section for bucket "1" would look something like this (assumes that "SSCurDocAmts" is in column "A" starting in row 5):


AA1: 'SSAgingBuckets
AB1: 'RowVizFormula
AA2: 1
AB2: =SUBTOTAL(9, A5) > 0

Then the total for bucket "1" can be obtained from this (assumes that "SSCurDocAmts" is in column "A&quot:

H1: 'Bucket 1
H2: =DSUM($A$5:$Z$5000,1,$AA$1:$AB$2)


Seems simple, but it isn't giving me accurate numbers yet (it's returning bucket totals greater than the sum of all the invoices).

Anyone know how I can get this to work?



VBAjedi

 

[VBAjedi]

Aha! Got it. I had some negative amounts in my data, so:

AB2: =SUBTOTAL(9, A5) > 0

should be:

AB2: =SUBTOTAL(9, A5) <> 0

Hot diggety! I think it's gonna work. . .


VBAjedi