Solved

Array Formulas working with Filters

Posted on 2012-03-13
5
354 Views
Last Modified: 2012-03-13
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.
0
Comment
Question by:belkingts
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:McOz
ID: 37714108
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
0
 

Author Comment

by:belkingts
ID: 37714136
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
0
 
LVL 9

Accepted Solution

by:
McOz earned 500 total points
ID: 37714607
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
0
 

Author Closing Comment

by:belkingts
ID: 37714626
Thanks very much for your time, will impliment one or both of these...
0
 
LVL 9

Expert Comment

by:McOz
ID: 37714643
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now