Return Max value of two columns IF date criteria is met

1) Put this array formula into G1 and confirm it by pressing CTRL-SHIFT-ENTER:

=MAX(IF($A$3:$A$13=$F3, B3:B13+0, ""))

You will see curly braces { } appear around your formula to indicate an array is active in that cell. If you do not, try again. Ctrl-Shift-Enter will activate the array.

2) Now copy that down as needed. 6/29/2012 works because it's in the data range, 7/1/2013 does not.

3) Put this array formula in H3

=MIN(IF($A$3:$A$13=$F3, C3:C13+0, ""))



4) When you expand those formulas later to encompass more data, don't try to use a whole column, that won't work.

No, what he said was that it will not work if you USE an entire column (B:B), not if you drag it across columns

if it's not working for you, are you sure you entered it by holding doun the CRTL and SHIFT keys together, and then pressed enter (while CTRL SHIFT are still pressed down)?

I don't recommend using the whole column (it can be slow) but there's no reason it won't work in Excel 2007 or later versions, e.g. this works for me in Excel 2010

=MAX(IF(A:A=$F3,B:C+0))

confirmed with CTRL+SHIFT+ENTER

Originally Posted by Medir

If I try your formula it returns #VALUE, and if I remove the +0 the formula returns 0. What am I doing wrong? By the way, I need the max value of the two columns combined but that is easy by simply changing it into =MAX(IF($A$3:$A$13=$F3, B3:C13+0, "")).

When you enter the formula and press CTRL-SHIFT-ENTER, so the curly braces appear around the formula? If not, try again. You need the braces to appear, the array must be active, else you'll get a #VALUE error.

Medir,

If your query has been answered to your satisfaction (as your post above suggests), could you please mark this thread as SOLVED - you can do this by using the Thread Tools, and clicking on "Mark thread as solved".

Also, as a newcomer, you may not know that you can thank those who assisted you by clicking on the * icon at the bottom left of any post you found helpful.

And last, but not least - welcome to the forum!