+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Find out Maximum date with two criteria

  1. #1
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,803

    Excel 2007 : Find out Maximum date with two criteria

    In excel 2007 i have three sheets.
    In sheet1 : Cell : "D3" : I have find out & show maximum date with two criteria (i.e. code & series) from all sheets.
    Also in cell : "E3 : I have find out maximum date with two criteria from particular one sheet only.
    I have mentioned comments in attach file.
    In sheet name 1112 & 1213 : I have a lot of data approx 40000.
    Column A : Invoice no
    Column B : Invoice date
    Column C : Code
    Column D : oano
    Column E : Name
    Column F : city
    Column G : distric
    Column H : Series

    In yellow highlighted cell i required formula.
    Attached Files Attached Files

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Excel 2007 : Find out Maximum date with two criteria

    In D3 use this array formula

    =MAX(IF(('1112'!$C$2:$C$7=$A3)*('1112'!$H$2:$H$7=D$2),'1112'!$B$2:$B$7))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,803

    Re: Excel 2007 : Find out Maximum date with two criteria

    This work perfectly but i also required to find out maximum date from multiple sheets with two criteria.

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,803

    Re: Excel 2007 : Find out Maximum date with two criteria

    Bump no reply. I have find out maximum date from multiple sheets with two criteria.

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,226

    Re: Excel 2007 : Find out Maximum date with two criteria

    Hi,

    So if you simply change the sheet name in Ace_XL's solution from 1112 to 1213 you'll have the max for the latter as well (since your tables in each sheet are of identical dimensions, nothing else in his formula need be amended).

    You can then take the 'max of these two maxes' to get your desired result.

    Regards
    Click * below if this answer helped

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

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,803

    Re: Excel 2007 : Find out Maximum date with two criteria

    Yes i know that. But i dont want to changed every time formula. So i required single formula calculate from multiple sheets with two criteria. Our data record at least 40,000+ records.

+ 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] Calculate due date based on multiple criteria in Excel 2007
    By allienzaddicts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-21-2013, 04:29 PM
  2. Replies: 4
    Last Post: 03-16-2013, 08:33 AM
  3. Replies: 8
    Last Post: 11-19-2012, 06:58 PM
  4. Find maximum and minimum value for each date
    By anna.gladkova in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2012, 08:50 PM
  5. [SOLVED] Find Maximum date
    By sgl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-15-2006, 04:10 AM

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