Excel 2007 : Find out Maximum date with two criteria

[avk]

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.

[Ace_XL]

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.

[avk]

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

[avk]

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

[XOR LX]

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

[avk]

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.