+ Reply to Thread
Results 1 to 5 of 5

Having an issue with a sumproduct formula once filtered data

  1. 08-14-2016, 12:26 PM #1
    jando4
    jando4 is offline
    Registered User
    Join Date
    08-14-2016
    Location
    New Hampshire
    MS-Off Ver
    Excel 2016
    Posts
    16

    Having an issue with a sumproduct formula once filtered data

    Hi-
    This is my first post....I am a middle school math educator creating a spreadsheet to track student growth for this coming school year. I am having difficulty with a sumproduct formula once I filter the data by class. Below is the formula I am using to count the number of students that are at or above a cutoff percent.
    In cell $O$12, is the chosen percentage cutoff. The column of data with student percentages is in cell range I19:I169.

    SUMPRODUCT(SUBTOTAL(3,OFFSET(I19,ROW(I19:I168)-ROW(I19),0)),(I19:I168=">="&$O$12)+0)

    The formula is giving a "0" when I want the data to count the number of students that meet the requirement.

    Thank you
    J
    Reply With QuoteRegister To Reply
  2. 08-14-2016, 12:45 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,060

    Re: Having an issue with a sumproduct formula once filtered data

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-14-2016, 01:51 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Having an issue with a sumproduct formula once filtered data

    Try it like this...

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(I19,ROW(I19:I168)-ROW(I19),0)),--(I19:I168>=$O$12))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 08-14-2016, 05:34 PM #4
    jando4
    jando4 is offline
    Registered User
    Join Date
    08-14-2016
    Location
    New Hampshire
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: Having an issue with a sumproduct formula once filtered data

    Thank you Tony. I don't fully understand your formula with the -- but it worked!
    Reply With QuoteRegister To Reply
  5. 08-14-2016, 05:52 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Having an issue with a sumproduct formula once filtered data

    See this...

    http://xldynamic.com/source/xld.SUMPRODUCT.html
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Approximate match on sumproduct using filtered data
    By trisoldee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2015, 10:53 AM
  2. [SOLVED] UDF to sumproduct with filtered data only
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-07-2015, 09:02 PM
  3. Countifs/Sumproduct for filtered data, searching if cell contains text
    By Bellio3105 in forum Excel General
    Replies: 5
    Last Post: 06-04-2014, 09:41 AM
  4. [SOLVED] Filtered Data - Inserting Blank Row at the Beginning. Issue
    By Vinod Krishna.C in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2014, 05:32 AM
  5. [SOLVED] Excel VBA - Issue in Naming Filtered Range on a Filtered List.
    By Vinod Krishna.C in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-06-2014, 01:17 PM
  6. [SOLVED] SUMPRODUCT formula to mimic SUTOTAL(109,x) when table is filtered
    By ron2k_1 in forum Excel General
    Replies: 1
    Last Post: 05-17-2012, 04:55 PM
  7. Issue with copy/paste dates from filtered data
    By deacs in forum Excel General
    Replies: 3
    Last Post: 01-05-2006, 11:10 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1