+ Reply to Thread
Results 1 to 16 of 16

Using Subtotal on Filtered Data with an additional condition

  1. #1
    Registered User
    Join Date
    07-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    29

    Using Subtotal on Filtered Data with an additional condition

    I'm looking to add up filtered data using something like SUBTOTAL(109, range), where range is (say) $I9:$I$754. However, I only want to add those values where the contents in colum D of the row is (say) "B".

    So - I'm trying to apply another condition to the SUBTOTAL in addition to the Filter.

    Many thanks

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Subtotal on Filtered Data with an additional condition

    Hi

    You can use.

    =SUMPRODUCT(--(D2:D754="B"),SUBTOTAL(9,OFFSET(I2:I754,ROW(I2:I754)-ROW(I2),0,1)))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    08-06-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Using Subtotal on Filtered Data with an additional condition

    Hi i am giving with example

    a2 A 10
    a3 A 20
    a4 A 30
    a5 B 100
    a6 B 100
    a7 C 25
    a8 C 14


    so now you want total only if column b contains A
    then apply following formula

    Please Login or Register  to view this content.

    PLEASE let me know if it is working?

  4. #4
    Registered User
    Join Date
    07-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Using Subtotal on Filtered Data with an additional condition

    Quote Originally Posted by Tanya1 View Post
    Hi i am giving with example

    a2 A 10
    a3 A 20
    a4 A 30
    a5 B 100
    a6 B 100
    a7 C 25
    a8 C 14


    so now you want total only if column b contains A
    then apply following formula

    Please Login or Register  to view this content.

    PLEASE let me know if it is working?
    Thanks for response, but I believe that's incorrect as it does not take account of the Filter applied to the range. See my original post.

  5. #5
    Registered User
    Join Date
    08-06-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Using Subtotal on Filtered Data with an additional condition

    please share your file

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Subtotal on Filtered Data with an additional condition

    Post#2 ?????

  7. #7
    Registered User
    Join Date
    07-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Using Subtotal on Filtered Data with an additional condition

    Quote Originally Posted by Tanya1 View Post
    please share your file
    Your sample data above could be used. Imagine we have applied a filter so that only rows with the Col C value >= 30 are showing. So only rows 4-6 are visible (others are hidden by the filter):

    a4 A 30
    a5 B 100
    a6 B 100

    Now, I want to add up the Col C values where (say) Col B = "A". The sum value I want to get therefore is 30.

  8. #8
    Registered User
    Join Date
    07-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Using Subtotal on Filtered Data with an additional condition

    Quote Originally Posted by Fotis1991 View Post
    Hi

    You can use.

    =SUMPRODUCT(--(D2:D754="B"),SUBTOTAL(9,OFFSET(I2:I754,ROW(I2:I754)-ROW(I2),0,1)))
    Thanks for this. Can you explain why you need the OFFSET function, rather than just explicitly inserting the range here (eg. I2:I754)?

  9. #9
    Registered User
    Join Date
    08-06-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Using Subtotal on Filtered Data with an additional condition

    c if such is the case that is
    1. If i column a in which i want to take only those value which are >10
    2.and only if column b contains a
    i will not use filter for sure
    e.g

    30 A
    20 A
    25 A
    30 A
    10 B
    10 A
    40 C
    Please Login or Register  to view this content.
    or is it necessary for you to apply filter because with sumproduct u can apply multiple conditions and calculate sum accordingly.

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Using Subtotal on Filtered Data with an additional condition

    the offset function returns an array of discrete ranges each of which is evaluated by subtotal so that only the visible cells are included in the total-for hidden rows the subtotal returns 0
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  11. #11
    Registered User
    Join Date
    07-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Using Subtotal on Filtered Data with an additional condition

    Quote Originally Posted by JosephP View Post
    the offset function returns an array of discrete ranges each of which is evaluated by subtotal so that only the visible cells are included in the total-for hidden rows the subtotal returns 0
    Joseph - thanks for the explanation. However, if we use 109 instead of 9 as the first Argument in the SUBTOTAL, doesn't that do the same thing (ie. limit scope to the visible rows). [By the way, I'm using Excel 2010 if that's relevant.]

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Using Subtotal on Filtered Data with an additional condition

    no-whether you use 9 or 109 makes no difference here. without the offset function subtotal would return one total for the entire range. with the offset it returns an array of values (0 for filtered rows and the value for visible rows)

  13. #13
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Subtotal on Filtered Data with an additional condition

    As my English are not good, i'd like to thanks Joseph for all the explanations.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Using Subtotal on Filtered Data with an additional condition

    Quote Originally Posted by Fotis1991 View Post
    As my English are not good, i'd like to thanks Joseph for all the explanations.
    Thanks for your solution. It does work! I will have to do some more work to fully understand it though!

  15. #15
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using Subtotal on Filtered Data with an additional condition

    You are welcome!

    Thanks for the reb*.

    Pls don't forget to mark your thread as solved(see in my signature to see how to do this).

  16. #16
    Registered User
    Join Date
    07-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Using Subtotal on Filtered Data with an additional condition

    Quote Originally Posted by JosephP View Post
    no-whether you use 9 or 109 makes no difference here. without the offset function subtotal would return one total for the entire range. with the offset it returns an array of values (0 for filtered rows and the value for visible rows)
    Joseph - thanks for the explanation. I need to do a bit more study (after a good night's sleep!)

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1