+ Reply to Thread
Results 1 to 6 of 6

Creating an Array formula which calculates depending on what is filtered

  1. #1
    Registered User
    Join Date
    11-12-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    3

    Question Creating an Array formula which calculates depending on what is filtered

    I have been trying to create an array filter which compares two non number values by looking it up in a table (the original values are formed of a letter and a number "4A, 4B, 4C" and are given a numerical value in a table by a match"13,12,11").

    Currently I am comparing both columns, which works, by the sum of an array formula:
    {=SUM(IFERROR(IF(NUMBERVALUE(MATCH(Y4:Y94,GR,0))=NUMBERVALUE(MATCH(Z4:Z94,GR,0)),1,0),0))}

    However, I want to be able to use filters in my table so values are ignored if they are hidden. When I use the aggregate to ignore hidden cells, I get the error "The value used is the wrong data type". I have included the formula below:

    {=AGGREGATE(9,7,IFERROR(IF(NUMBERVALUE(MATCH(Y4:Y94,GR,0))=NUMBERVALUE(MATCH(Z4:Z94,GR,0)),1,0),0))}

    The bold part is a direct copy from the previous formula. Because of this I am confused, looking at the formula it is returning 0's and 1's yet gives me the error #VALUE. Any ideas?

    Thanks!!

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,188

    Re: Creating an Array formula which calculates depending on what is filtered

    I believe that AGGREGATE (x,y,RANGE) work only cases:
    1) RANGE is specific range in worksheet, for instant, A1:G10
    2) If RANGE was created from formula (like in your sample), it works for only case: x=14 or 15 (max or min),
    other x value, AGGREGATE does not support.

  3. #3
    Registered User
    Join Date
    11-12-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Creating an Array formula which calculates depending on what is filtered

    Thank you, that's a great explanation. Is there any alternative formula's you would recommend using?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,507

    Re: Creating an Array formula which calculates depending on what is filtered

    Hello Dandruce and Welcome to Excel Forum.
    It may help someone propose a solution if we could see a sample .xlsx file, instructions are given in the banner at the top of the page.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    11-12-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Creating an Array formula which calculates depending on what is filtered

    Thank you for the assistance, I found a way of doing this. Basically a sum product and multiplying the original array by a subtotal which gives a 1 for visible cells and a 0 for hidden ones.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,507

    Re: Creating an Array formula which calculates depending on what is filtered

    Glad that you found a solution. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Array Formula which ignores blank cells / only calculates cells with values
    By LordByron in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-02-2020, 06:47 AM
  2. [SOLVED] Index/Aggregate formula calculates correct row but does not yield name from Index array
    By FivestarMac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2019, 05:07 PM
  3. Create macro that calculates the same formula depending on how much data
    By ElPedro in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-20-2017, 03:49 AM
  4. Creating an Array from range, no duplicates, filtered by location
    By gottnoskill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-13-2015, 10:14 AM
  5. Creating filtered array within formula before then calculating percentil
    By bpomeroy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-05-2014, 09:52 AM
  6. Replies: 7
    Last Post: 06-20-2008, 02:54 AM
  7. Creating a formula that calculates a total cell
    By Growers in forum Excel General
    Replies: 4
    Last Post: 08-01-2006, 04:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1