+ Reply to Thread
Results 1 to 4 of 4

sumproduct using filters

  1. #1
    Registered User
    Join Date
    05-13-2021
    Location
    RAleigh,nc
    MS-Off Ver
    10
    Posts
    2

    Question sumproduct using filters

    HI all

    trying to see if it is possible to use a filter with the sumproduct. I want to be able to filter and have the number change.


    Thanks
    Jerry
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: sumproduct using filters

    I used a helper column to achieve this. The Helper column can be hidden after the formula is inputted.

    In L5 copied down as far as you expect data to go
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then modify your SUMPRODUCT Formula to
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Why this works:
    The formulas in column F return 1 when they are unhidden (and there is something in col A) but return 0 when hidden.
    Will this work for you?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-13-2021
    Location
    RAleigh,nc
    MS-Off Ver
    10
    Posts
    2

    Re: sumproduct using filters

    thanks


    can i ask what the {} does because when i enter it into my formual it doesn't work
    and why what would cause value errors?

    Thanks

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: sumproduct using filters

    Where do you see {} ? That would be what Excel adds to denote an Arrayed formula. I didn't use any arrayed formulas.

+ 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. SUMPRODUCT and Filters
    By bmcraney in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-26-2019, 01:22 PM
  2. [SOLVED] Get SUMPRODUCT change dynamicaly with filters
    By Chatcola in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2019, 09:28 AM
  3. [SOLVED] SUMPRODUCT that filters result with each new condition added
    By iantix in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-24-2017, 07:20 AM
  4. [SOLVED] Sumproduct that filters out errors vlaues
    By Craig K. in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-21-2013, 04:23 PM
  5. [SOLVED] SUMIFS, SUMPRODUCT with filters etc
    By BC Rob in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-06-2013, 02:49 PM
  6. SUMPRODUCT Function with Filters
    By R_ka_Tect in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-18-2013, 10:03 AM
  7. SUMPRODUCT and filters
    By richandjo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2011, 03:48 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