MAXIF on dates

RFurball

New Member
Joined
Jul 26, 2004
Messages
24
I searched through the many MAXIF posts and can not find a solution to my problem. Lets say I have a two column list, first column is date and second column is quantity. What I am looking for if the maximum value from a particular year.

I tried =MAX(IF(YEAR(A2:A12)=2017,B2:B12)) but it returns zero. I think the YEAR(A2:A12) is not valid for a logical compare. Any help on this formula would be appreciated.

My data might look like this:
Year Quantity
04/01/17 9.0
05/25/17 3.0
08/15/17 7.0 Year Max
02/01/18 2.0 2018 0
03/15/18 4.0
05/15/18 5.0
09/06/18 6.0
12/15/18 2.0
01/15/19 9.0
02/15/19 3.0
03/12/19 4.0

and when I enter a year I wan the max to be found in column B for that year.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Excel Workbook
ABCD
1YearQuantity
24/1/20179
35/25/20173
48/15/20177YearMax
52/1/2018220186
63/15/20184
75/15/20185
89/6/20186
912/15/20182
101/15/20199
112/15/20193
123/12/20194
Sheet1
 
Upvote 0
Thanks that works, I have used CTRL SHIFT ENTER before but I forgot what it did. I thought I used it to sum across worksheets or something like that.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top