Array Formulas working with Filters

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.
belkingtsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

McOzCommented:
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
belkingtsAuthor 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
McOzCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
belkingtsAuthor Commented:
Thanks very much for your time, will impliment one or both of these...
McOzCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.