+ Reply to Thread
Results 1 to 7 of 7

SUMIF Function for cells that will be filtered (SUBTOTAL)

  1. #1
    Registered User
    Join Date
    01-06-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    11

    SUMIF Function for cells that will be filtered (SUBTOTAL)

    I have attached an excerpt from my excel sheet.


    I need to sum by category (the different markups). I have worked this out previously using a SUMIF functions which you will see used in the table on right.

    I would like to use embed the SUBTOTAL, as I will be filtering some cells in my main sheet. How can I incorporate into my calculation the subtotal.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,093

    Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

    M3 cell , formula , drag down

    HTML Code: 
    =SUMPRODUCT(SUBTOTAL(9,OFFSET($G$1,ROW($1:$12),,,))*($B$2:$B$13=$L3))

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,130

    Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

    Another option with a helper column to avoid volatile functions.
    In I2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and then in M3 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-06-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    11

    Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

    Could you explain you mean by volatile?

  5. #5
    Registered User
    Join Date
    01-06-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    11

    Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

    Quote Originally Posted by wk9128 View Post
    M3 cell , formula , drag down

    HTML Code: 
    =SUMPRODUCT(SUBTOTAL(9,OFFSET($G$1,ROW($1:$12),,,))*($B$2:$B$13=$L3))
    Hi this solves my problem, thanks. Would it be possible for you to explain what the offset part of the function does?

  6. #6
    Registered User
    Join Date
    01-06-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    11

    Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

    Quote Originally Posted by Fluff13 View Post
    Another option with a helper column to avoid volatile functions.
    In I2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and then in M3 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Could you explain you mean by volatile?

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,130

    Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

    A volatile function (such as offset) will recalculate whenever any cell in the workbook is changed, or any formula recalculates.
    Depending on what you workbook is like, volatile functions could make the workbook very slow.

+ 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] Subtotal not showing filtered out cells
    By nicko54 in forum Excel General
    Replies: 2
    Last Post: 05-11-2020, 01:14 AM
  2. [SOLVED] Subtotal-like function for text on a filtered table
    By ericbinfet in forum Excel General
    Replies: 1
    Last Post: 01-09-2014, 04:27 PM
  3. How to subtotal SUMIF based on filtered cells
    By Nanny Pam in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2013, 09:18 AM
  4. Counting filtered cells using SUBTOTAL
    By Russell Dawson in forum Tips and Tutorials
    Replies: 7
    Last Post: 10-07-2012, 12:11 PM
  5. Count filtered cells using SUBTOTAL
    By Russell Dawson in forum Excel Tips
    Replies: 2
    Last Post: 07-15-2012, 03:39 PM
  6. [SOLVED] is date can be filtered to a different cell?-subtotal or any other function?
    By Morphyus C via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-29-2005, 11:05 AM
  7. [SOLVED] Subtotal function with Filtered Data
    By RonB in forum Excel General
    Replies: 3
    Last Post: 08-12-2005, 06:05 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