Excel 2007 : Find out Maximum date with two criteria

1. 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.

2. 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.

3. 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. 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. 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

6. 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.

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

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