+ Reply to Thread
Results 1 to 10 of 10

MAX if with multiple criteria and if column is not false

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    7

    MAX if with multiple criteria and if column is not false

    I am trying to get the value from a specific cell from a large range of data. The only criteria i have is the day, so basically i need to find the highest value in a specific column that has a specified day to it (day in column A for example).

    But the value i need must meet the criteria if another column contains data.

    That probably doesn't make much sense so here is my example:

    Say for example I have 4 columns with large amounts of data covering a time period as you can see from the below example;

    example.jpg

    Now what i would like to do is get the value for the highest in the 'values' column where the day = 01 but only if the country column has a value.

    Basically my goal is to grab the top ten highest values where the country column is not empty and place those values in another range of cells listing the top ten values from each country. then of course i will need to pull those country values to another range of cells to display those countries with their respective values.

    I hope i am making sense with all this, please forgive me if i am not. The actual sheet i am pulling this from will be from a DB with thousands of rows all with various data. This is the bit where i am getting stuck. If any one could help me out with this i would be very grateful. Cheers

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: MAX if with multiple criteria and if column is not false

    Hi robolist,

    welcome to the forum.
    Please upload a sample workbook (not image) along with your expected results. Thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    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: MAX if with multiple criteria and if column is not false

    See this link about LARGE(IF.. function.

    http://www.dailydoseofexcel.com/arch...eif-functions/
    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.

  4. #4
    Registered User
    Join Date
    02-06-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    7

    Re: MAX if with multiple criteria and if column is not false

    Quote Originally Posted by dilipandey View Post
    Please upload a sample workbook (not image) along with your expected results. Thanks.
    Hi DILIPandey,
    Thank you so much for your reply, I have attached a sample workbook as you suggested.

    Basically you will see the first sheet - 'MainSheet' needs to pull the correct data from the second sheet - 'DataSheet'. You can see that some fields do not have the country cell populated, this is typical in the way i pull my data from the DB. So i need to run the top ten countries with their respective values that correspond to the correct day. My typical workbook will be containing thousands of rows with around ten columns but for this example these are the only data sets i need. I am including a fourth column in this example as an illustration that there are multiple columns on the sheet.

    Many thanks in advance for your help.

    Cheers

    http://wikisend.com/download/250482/...-workbook.xlsx
    Last edited by robolist; 02-06-2013 at 05:02 AM. Reason: Forgot to attach link to sample workbook

  5. #5
    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: MAX if with multiple criteria and if column is not false

    Really kind reply, BUT:

    1) Is it kind to ignore others suggestions?

    2) Did you check the provided link and does not works for you?

    3) Do you prefer to solve your problem someone else and not just to helps you?

    4) NO attachment!

  6. #6
    Registered User
    Join Date
    02-06-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    7

    Re: MAX if with multiple criteria and if column is not false

    Quote Originally Posted by Fotis1991 View Post
    Really kind reply, BUT:

    1) Is it kind to ignore others suggestions?

    2) Did you check the provided link and does not works for you?

    3) Do you prefer to solve your problem someone else and not just to helps you?

    4) NO attachment!
    FYI I was getting around to replying to you. But i am actually trying to do a hundred things at the same time, I wanted to reply in the same order, and was actually reading through the link you sent. So thank you. and no i do not expect people to do the work for me...

    See my edited response with attachment

  7. #7
    Registered User
    Join Date
    02-06-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    7

    Re: MAX if with multiple criteria and if column is not false

    Quote Originally Posted by Fotis1991 View Post
    See this link about LARGE(IF.. function.

    http://www.dailydoseofexcel.com/arch...eif-functions/
    Many thanks for your reply, i really was getting around to reply to you, i just wanted to read through the link first. I am actually trying to get other work done at the same time as trying to solve this problem. Thanks for sharing that link with me it helped a bit, but i dont think it does what i need it to do as there are empty cells that i need to take into consideration and the final sheet at the end of the month will contain thousands of rows if not hundreds of thousands.

    But your link did get me thinking and perhaps i was looking at this backwards, i was trying to get the number first, but perhaps i need to get the country first and then the number from the country. Perhaps an index match max is needed for this task. I tried something like that but then it just returned 0 because the highest number in that match that column was empty. I need to only lookup values that has the country field populated.

    I hope there is a way round this problem. If anyone can help i would be very grateful. I am not asking anyone to do the work for me, far from it, I have been trying to do this for several days now and only just now coming to this forum. I am just getting rather brain dead on this whole formula and needed some assistance form someone who is more Excel pro.

    Many thanks.

    http://wikisend.com/download/250482/...-workbook.xlsx

  8. #8
    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: MAX if with multiple criteria and if column is not false

    ...hope there is a way round this problem. If anyone can help i would be very grateful. I am not asking anyone to do the work for me, far from it, I have been trying to do this for several days now and only just now coming to this forum
    As i know how someone that looks for a solution to his problem feels, see if this take care you needs.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-06-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    7

    Re: MAX if with multiple criteria and if column is not false

    Quote Originally Posted by Fotis1991 View Post
    As i know how someone that looks for a solution to his problem feels, see if this take care you needs.
    Hey thanks so much for that man. Hopefully i can sleep well tonight now hahaha.

    Thats perfect, thank you so much for your help

  10. #10
    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: MAX if with multiple criteria and if column is not false

    You are welcome and for beeing(again) the bad quy here, pls, next time don't upload your example somewhere else. Only to our forum. Like i did!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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