+ Reply to Thread
Results 1 to 3 of 3

How to subtotal SUMIF based on filtered cells

Hybrid View

  1. 02-19-2013, 11:54 PM #1
    Nanny Pam
    Nanny Pam is offline
    Registered User
    Join Date
    02-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    How to subtotal SUMIF based on filtered cells

    Hello - I've been looking through many posts and have gotten some great information, but can't seem to fix my formula. I have a SUMIF formula that works well, but when I filter on another column, the formula does not disregard the hidden cells. It continues to calculate based on all the cells. Please help!

    Here is the formula. Column F is the type of work being performed. Column D is the amount of hours spent working in that category. This part is currently working as designed. However, I have another column that contains the department name - this is what I would like to filter by and obtain a subtotal for.

    =SUMIF(Table!$F$2:$F$46,"Enhancement Spec",Table!$D$2:$D$46)+SUMIF(Table!$F$2:$F$46,"Enhancement Investigation",Table!$D$2:$D$46)

    Any help is appreciated! Thanks!
    Reply With QuoteRegister To Reply
  2. 02-20-2013, 12:12 AM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: How to subtotal SUMIF based on filtered cells

    hi Nanny Pam, try:
    Formula: copy to clipboard 
    =SUMPRODUCT((SUBTOTAL(3,OFFSET($F$1,ROW($F$2:$F$46)-ROW($F$2)+1,)))*($F$2:$F$46={"Enhancement Spec","Enhancement Investigation"})*($D$2:$D$46))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  3. 02-20-2013, 09:18 AM #3
    Nanny Pam
    Nanny Pam is offline
    Registered User
    Join Date
    02-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: How to subtotal SUMIF based on filtered cells

    It works!!!! Thank you Thank You! Thank You!
    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)

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