We help IT Professionals succeed at work.

# Array Formulas working with Filters

belkingts asked
on
Hello. I am trying to add an Array Formula to a spreadsheet that will work if a user then filters by a specific Field. e.g in column A we have countries. At top of spreadsheet I have Array formula for total.

when user selects filter to show only France (list in column could be UK, Spain France etc)  I want to see the total Change to show only array result for France not all data...

Thought best way was to add an If statement to the Array but for this I need to know in a specific field what the user has selected...

Example Col A Region, Col B is Units Sold and Col C is the Unit price.

Running array on Column B*Column C by all rows in data set wish to know the filtered total not just the grand total.
Comment
Watch Question

## View Solution Only

Commented:
Haven't tested this, but I think it should work --
Have you tried using the SUBTOTAL function? This has built-in functionality to sum (or average, or whatever) only the filtered results.

So if you have the following table, and it is filtered to show only France records:

B               C               D
+---------------+---------------+---------------+
1 |  France  |      10     |     \$10    |
+---------------+---------------+---------------+
2 |   Spain   |       5      |     \$10    |
+---------------+---------------+---------------+
3 |  France  |      10     |     \$10    |
+---------------+---------------+---------------+

=SUBTOTAL(9,C:C*D:D)  (entered as an array formula)

should evaluate to:

=SUBTOTAL(9,{\$100;\$100})

=\$200

Commented:
Sorry Tried that and Subtotal does not appear to work with Array Formula's. Really wish it did as this would be so cool....using Excel 2003 by the way in case that makes a difference....

So far i have tried the If statement adding into array which seems to work but only on a specific filed I choose to show something not a user selecting the filter.

As a side line anyway to show the filter request made by a User. e.g If they select a filter on Row 9 which is start of dataset that when they select France in filter I have a field which will show France as the request in say A1
Commented:
Shucks. Well, as a workaround, you could add a column which does the multiplication and use subtotal on that.

As for displaying the filtered value, what happens if the user wants to display multiple countries? Using the array approach could get complicated.

You could still display the first filtered value if you added another column (could be hidden) which has the row number, and then get the min rownumber in the filtered results and use it to get the corresponding country name (see the attached example workbook). Again, this will only display the first country, which is fine if you are only displaying one country but meaningless otherwise.

The formula to do this would be something like:

=INDEX(A2:A4,SUBTOTAL(5,D2:D4),1)

Where column D has row numbers and Column A has Country Names.

Hope this helps,

McOz
SubtotalExample.xlsx

Commented:
Thanks very much for your time, will impliment one or both of these...

Commented:
Please note the edit -- I think we might have crossed wires on the last comment -- you would need to use the additional column or if you had  different unit prices for the same country the total would not be weighted correctly. Sorry about that.

McOz