Maxif

=MAX(IF( (Data!A:A =Report!$B$1) * (Data!B:B=Report!A4) * (Data!E:E=2012), Data!I:I))

... confirmed with ctrl+shift+Enter

I would change that to eliminate full-column references.

Try to multiply the 3 criteria together, returns 1 if TRUE, 0 if FALSE, then multiply with I:I to get list of value of I:I that is TRUE, then MAX:
Something like:

Please Login or Register  to view this content.

confirmed with Ctrl-shift-enter

What does the { do?

http://office.microsoft.com/en-us/ex...001087290.aspx

And I didn't get the comment about full-column references.

Use only the range you need:

=MAX(IF( (Data!A1:A100 =Report!$B$1) * (Data!B1:B100=Report!A4) * (Data!E1:E100=2012), Data!I1:I100))

@bebo: If all the values in col I are negative, your formula will return 0 as the maximum.

The reason it's laggy is because you are looking at the entire column. Choose a range that you know your data will fit into, i.e
=MAX(IF( (Data!A2:A100 =Report!$B$1) * (Data!B2:B100=Report!A4) * (Data!E2:E100=2012), Data!I2:I100))

The {} signifies you've set the function up as an Array function (when you hit CNTRL SHFT ENTER) which means excel will set aside memory for intermediate calculations. See;
http://office.microsoft.com/en-us/ex...001087290.aspx

EDIT: Too slow for shg! LOL. We even gave the same examples and link. I might need medical help.

Hi whatever61

Or may be using the aggregate!

Formula:

Please Login or Register  to view this content.