+ Reply to Thread
Results 1 to 17 of 17

SUBTOTAL for MAX/MIN function ignoring certain values

  1. #1
    Registered User
    Join Date
    03-05-2015
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    8

    Question SUBTOTAL for MAX/MIN function ignoring certain values

    I have the following table:

    John 8
    Jean 7
    Jane 100
    Jean 9
    Jeff 4
    John 100
    Jean 8
    John 7
    Jane 5

    If I filter the above table on John, I will have

    John 8
    John 100
    John 7

    The function =SUBTOTAL(4,B2:B10) will give the value 100. However I would want the SUBTOTAL function to ignore the particular value 100, so that =SUBTOTAL(4,B2:B10) will give 8 in the above case.

    How would I achieve this? Thanks in advance.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Last edited by sandy666; 03-05-2015 at 03:48 AM.

  3. #3
    Registered User
    Join Date
    03-05-2015
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    8

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Thanks sandy666.. but your solution will not work for me.

    =sumif(B2:B10,"<>" & 100) will give 48 - which is the sum of all the values in the column, ignoring the values 100.

    The function I am looking for isn't SUM but MAX. Also, I want to apply the MAX function on a filtered list.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    #2 was updated, check again

    ok, got it "isn't SUM but MAX"
    Last edited by sandy666; 03-05-2015 at 03:59 AM.

  5. #5
    Registered User
    Join Date
    03-05-2015
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    8

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    =SUMPRODUCT(SUBTOTAL(4,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1)),--(B2:B10<>100)) gives 15 - sum again

    I need MAX

  6. #6
    Forum Expert 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: SUBTOTAL for MAX/MIN function ignoring certain values

    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.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    try
    Please Login or Register  to view this content.
    ctrl+shift+enter

    edit:
    change references of course
    Last edited by sandy666; 03-05-2015 at 05:49 AM.

  8. #8
    Registered User
    Join Date
    03-05-2015
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    8

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Thanks Fotis1991.. I did visit that thread before I even posted here. But I couldn't fully decipher how to use that formula

    =MAX(IF(Criteria_Field=B2,SUBTOTAL(109,OFFSET(Value_Field,ROW(Value_Field)-MIN(ROW(Value_Field)),0,1))))

    in my worksheet.

    Would be grateful if someone can help me out.. thanks.

  9. #9
    Registered User
    Join Date
    03-05-2015
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    8

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Thanks sandy666... the formula

    =MAX(IF(J11:J19<>100,SUBTOTAL(109,OFFSET(J11:J19,ROW(J11:J19)-ROW(J11),0,1))))

    just did the trick, though I had to replace the referenced ranges for my table.

    Just a couple of questions:

    1. The SUBTOTAL function uses 109 which is for SUM, so how does it find the MAX here?

    2. How would I modify this formula to find MIN and AVERAGE as well

    Thanks again sandy666..

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    ad.2 change MAX to MIN or AVERAGE
    ad1. array formula: partial result: =MAX(IF(J11:J19<>100,{8;7;100;9;22;100;15;7;5}))
    Last edited by sandy666; 03-05-2015 at 06:16 AM.

  11. #11
    Registered User
    Join Date
    03-05-2015
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    8

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Quote Originally Posted by sandy666 View Post
    ad.2 change MAX to MIN or AVERAGE
    I tried doing that.. and the formula works on unfiltered data.

    But if the table is filtered, MIN gives zero and AVERAGE gives some other non-zero value
    (and i did use ctrl+shift+enter)

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    ups, right. formula should be adapted to filtered MIN and AVERAGE.
    maybe you will get any good advice about it from Excel Gurus

    for MIN
    Please Login or Register  to view this content.
    Last edited by sandy666; 03-05-2015 at 06:38 AM.

  13. #13
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).http://www.excelguru.ca/forums/showt...ed=1#post17692

  14. #14
    Registered User
    Join Date
    03-05-2015
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    8

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Quote Originally Posted by Pepe Le Mokko View Post
    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).http://www.excelguru.ca/forums/showt...ed=1#post17692

    Oops.. I posted this issue on another forum only AFTER I found no further responses to my issue and I was a bit hard-pressed to get this resolved (I also made it known in the other forum that this is a re-post). Apologies.. I wasn't aware that I have to post the link to this post, so I will make sure to include the link to this post on the other forum.

    Last but not least, I surely appreciate the time spent by all those who go through each post and try to provide solution..

  15. #15
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Next time, have a look at forum rules. Every forum will draw your attention to that problem

  16. #16
    Registered User
    Join Date
    03-05-2015
    Location
    UAE
    MS-Off Ver
    2010
    Posts
    8

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    Quote Originally Posted by sandy666 View Post
    ups, right. formula should be adapted to filtered MIN and AVERAGE.
    maybe you will get any good advice about it from Excel Gurus

    for MIN
    Please Login or Register  to view this content.
    The MIN formula above works..
    Replacing AVERAGE for MIN works too..

    Thanks a ton, sandy666.. !!
    Last edited by sraghavan; 03-07-2015 at 01:23 AM.

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: SUBTOTAL for MAX/MIN function ignoring certain values

    You are welcome
    sandy

+ 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] MIN function ignoring values less then a certain number
    By Destroy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-02-2015, 04:15 PM
  2. [SOLVED] Rank function ignoring zero and negative values
    By mfortier3 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-12-2013, 05:37 PM
  3. Function to Choose and List Values ignoring blanks and erros
    By k2i2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-16-2013, 11:10 AM
  4. Ignoring Negative Values in SUM function
    By Rocksteady in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-30-2013, 11:18 AM
  5. Subtotal ignoring intermediate SumIf
    By natesgotjunk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-04-2013, 01:54 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