+ Reply to Thread
Results 1 to 9 of 9

SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

  1. #1
    Registered User
    Join Date
    06-17-2012
    Location
    Riga
    MS-Off Ver
    Excel 2010
    Posts
    11

    SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    Hello,

    I have a database (sample attached) and I'd like to pull data from it onto a "summary statement" in a new tab. The summary statement will include the same criteria as the database fields. The database is as follows:

    Property # (Column A)
    Account # (Column B)
    Date (Columns C-E, and it will continue over as time goes on)
    Actual Data (C2:E7, in this example)

    The Summary Statement will still have all the Accounts listed as in the database, and it will have the Dates (years, or months) as well. The catch is that I would like to have a section where the user/manager could input 1 or multiple Properties as a Filter, and the Summary Statement would be populated with the Actual Data based on which Properties are inputted.

    I am looking for a formula that achieves this.

    In the attachment, I can get close coming at it from two different angles, but I can't get it right:

    Formula 1 - Filters the data together by Account and whatever Properties are listed in the Property Filter (A2:A11). BUT, I only know how this works with one sum column, and in this example it is 2013 (not including 2014 or 2015) from the Database. Uses SumProduct and IsNumber.

    Formula 2 - Allows user to filter data for any Property, Account, and under the correct Date on the summary statement; BUT, only 1 Property can be selected in this formula. Uses SumProduct.

    It would be great if the second formula can integrate the filter array allowing for the selection of multiple Properties as Formula 1 does. But catching the data for all rows/years. Essentially need to "combine" both formulas into one!

    Anyone's assistance on this is much appreciated and thank you in advance. Happy new year!
    Last edited by matt303; 01-06-2016 at 10:33 AM.

  2. #2
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    Hi
    If you're using an Access query as your data source, select 'pivot Table' when going through the wizard. This will allow you to filter the data however you like.
    Frob first, tweak later

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    Try

    =SUMPRODUCT(Database!$C$2:$E$7*(Database!$B$2:$B$7=$C5)*ISNUMBER(MATCH(Database!$A$2:$A$7,$A$2:$A$11,0)))

  4. #4
    Registered User
    Join Date
    06-17-2012
    Location
    Riga
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    Hi Neil,
    Thank you for pivot table suggestion. I have used them before, but I am thinking if I can find a formula here it would be better suited, as I want to actually pull data from two databases in my real file. One database will include past/actual numbers, and one will include future projections.
    Hi Jonmo,
    Thanks for the suggestion. That formula does indeed capture all the data for that one filtered account in all years... but I would still be looking to filter them by year, as I have tried in my Formula 2?

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    I don't understand, why not just unlock the column reference of $C$2:$C$7 in your first formula, and drag it to the right?

    =SUMPRODUCT(Database!C$2:C$7,(Database!$B$2:$B$7=$C5)*ISNUMBER(MATCH(Database!$A$2:$A$7,$A$2:$A$11,0)))

  6. #6
    Registered User
    Join Date
    06-17-2012
    Location
    Riga
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    Hi Jonmo,
    Yes, I didn't make that necessity clear. The database is a simple example, but my actual database will have monthly columns, and the Summary Statement will have annual columns. Therefore, the date criteria in the SumProduct as I laid out in Formula 2 would still be necessary - to filter the monthly database columns into annual columns in the Summary Statement.
    Has me stuck!

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    Try

    =SUMPRODUCT(Database!$C$2:$E$7*(TEXT(Database!$C$1:$E$1,"yyyy")=TEXT(E$4,"yyyy"))*(Database!$B$2:$B$7=$C5)*ISNUMBER(MATCH(Database!$A$2:$A$7,$A$2:$A$11,0)))

  8. #8
    Registered User
    Join Date
    06-17-2012
    Location
    Riga
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    Wow, that's the one! Have never used the TEXT formula, will need to review.
    I checked, it works with months in the same year as well if monthly columns in the database.
    Thank you very much!!!

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SumProduct, IsNumber formulas possible to filter multiple column and row criteria?

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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