Avatar of belkingts
belkingts asked on

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.
Microsoft Applications

Avatar of undefined
Last Comment
McOz

8/22/2022 - Mon
McOz

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
ASKER
belkingts

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
ASKER CERTIFIED SOLUTION
McOz

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
belkingts

Thanks very much for your time, will impliment one or both of these...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
McOz

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