Discussion Re: How to find the max value corresponding to a date range in Excel
https://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3262455#M139120
<P><LI-USER uid="1124082"></LI-USER> </P><P>Other functions that could be used are MAXIFS or FILTER</P><LI-CODE lang="excel-formula">"Month"
= 1+EOMONTH(UNIQUE(Date),-1)
"Maximum count"
= MAXIFS(Count, Date, UNIQUE(Date))</LI-CODE><P>or, using Lambda functions and FILTER,</P><LI-CODE lang="excel-formula">"Maximum count"
= MAP(UNIQUE(Date),
LAMBDA(d,
MAX(FILTER(Count, Date=d))
)
)</LI-CODE><P> </P>Mon, 21 Mar 2022 11:21:29 GMTPeter Bartholomew2022-03-21T11:21:29ZHow to find the max value corresponding to a date range
https://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3260423#M138728
<P>Hello,</P><P> </P><P>hope you are all doing good.</P><P>I have to work on some Excel report and ...i'm not good in Excel so i hope someone could help me a bit <LI-EMOJI id="lia_face-with-tongue" title=":face_with_tongue:"></LI-EMOJI> ...and i guess i will come back several other times <LI-EMOJI id="lia_grinning-face-with-smiling-eyes" title=":grinning_face_with_smiling_eyes:"></LI-EMOJI></P><P> </P><P>ok so... I have a worksheet where are multiple dates and I have to extract a maximum value for each separate date range but make it correspond to the first day of the month. i mean ...</P><P> </P><TABLE width="139"><TBODY><TR><TD width="75">16/02/2022</TD><TD width="64">3</TD></TR><TR><TD>16/02/2022</TD><TD>1</TD></TR><TR><TD>16/02/2022</TD><TD>2</TD></TR><TR><TD>19/03/2022</TD><TD>5</TD></TR><TR><TD>20/04/2022</TD><TD>2</TD></TR><TR><TD>20/04/2022</TD><TD>1</TD></TR></TBODY></TABLE><P> </P><P>for 16/02/2022 max would be 3</P><P> 19/03/2022 max would be 5</P><P> 20/04/2022 max would be 2</P><P> </P><P>On another sheet, i would need to get the final max value from the previous sheet and make it correspond to </P><P> </P><P>01/02/2022 which would be 3</P><P>01/03/2022 which would be 5</P><P>01/04/2022 which would be 2</P><P> </P><P>Would someone please be able to help me do this ? <LI-EMOJI id="lia_disappointed-face" title=":disappointed_face:"></LI-EMOJI></P><P> </P><P>Thanks a lot for your time</P>Thu, 17 Mar 2022 19:19:45 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3260423#M138728Kaddrik2022-03-17T19:19:45ZRe: How to find the max value corresponding to a date range
https://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3260850#M138792
<P>Let's say your data is in Sheet1 (e.g Column A and B).<BR />And the formula you want to write is "B1" in the sheet below and your formula is calculated based on the 1st day of the month. If so, you can try as below.<BR /> A B<BR />1 1/2/2022 =MAXIFS(Sheet1!B:B, Sheet1!A:A,">="&A1, Sheet1!A:A,<BR /> "<="&eomonth(A1,0)<BR />2 1/3/2022 drag down formula above<BR />3 1/4/2022 drag down formula above<BR /><BR />Or, do you want the max of exactly the first date occured in the month?<BR />For e.g if there are 5/2/2022, 16/2/2022 and 20/2/2022 in Feb 2022, what you want is max of 5/2/2022. Please let me know.</P>Fri, 18 Mar 2022 09:59:52 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3260850#M138792Starrysky19882022-03-18T09:59:52ZRe: How to find the max value corresponding to a date range
https://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3260905#M138799
<P>hello <LI-USER uid="1319311"></LI-USER> </P><P> </P><P>thanks a lot for your reply.</P><P> </P><P>hmm...not sure it's like this...</P><P>in fact i would have in sheet1 , column A and B, this</P><P> A B</P><TABLE width="139"><TBODY><TR><TD width="75">16/02/2022</TD><TD width="64">3</TD></TR><TR><TD>16/02/2022</TD><TD>1</TD></TR><TR><TD>16/02/2022</TD><TD>2</TD></TR><TR><TD>19/03/2022</TD><TD>5</TD></TR><TR><TD>20/04/2022</TD><TD>2</TD></TR><TR><TD>20/04/2022</TD><TD>1</TD></TR></TBODY></TABLE><P> </P><P>and then in Sheet2 this ...</P><P> A</P><P>01/02/2022 and i have to populate column B with 3</P><P>01/03/2022 5</P><P>01/04/2022 2</P><P> </P><P>In the meantime i see my colleague created an intermediate sheet... let's call it Sheet3 ... where he shows only 1 occurence of the date</P><P> A B</P><P>16/02/2022 </P><P>19/03/2022</P><P>20/04/2022</P><P> </P><P>so i created in column B of Sheet3 a formula like MAXIFS(Sheet1!A:A,Sheet1!B:B,$A1)</P><P>Where A1 is date 16/02/2022. and this seems to give me the maximum value when i have several times the same date. I end up with </P><P> A B</P><P>16/02/2022 3</P><P>19/03/2022 5</P><P>20/04/2022 2</P><P> </P><P>but then in sheet 3, imagining for 1 month i have several weeks that are reported with their maximum like this </P><P> </P><P>09/02/2022 23</P><P>16/02/2022 3</P><P>23/02/2022 10</P><P> </P><P>I still don't understand how i can have in sheet2 , the maximum value corresponding to 1 month</P><P> </P><P>01/02/2022</P><P>01/03/2022</P><P>01/04/2022</P><P> </P><P>which for 01/02/2022 (if i take the previous example) would be 23</P><P> </P><P>is it clearer ?</P><P> </P><P>Thanks a lot for your time</P><P> </P><P> </P><P> </P>Fri, 18 Mar 2022 11:04:42 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3260905#M138799Kaddrik2022-03-18T11:04:42ZRe: How to find the max value corresponding to a date range
https://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3260980#M138809
<P>Dear <LI-USER uid="1124082"></LI-USER>,</P><P>What you want is maximum value for the whole month, then please see my previous reply.</P><P>It is for the whole month.</P><P>For individual date in the month, you can use your formula. Thanks</P>Fri, 18 Mar 2022 12:50:04 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3260980#M138809Starrysky19882022-03-18T12:50:04ZRe: How to find the max value corresponding to a date range
https://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3262421#M139117
<P>Hello <LI-USER uid="1319311"></LI-USER> ,</P><P> </P><P>Sorry for the late reply.</P><P> </P><P>I just tested what you explain and it works ! <LI-EMOJI id="lia_slightly-smiling-face" title=":slightly_smiling_face:"></LI-EMOJI> </P><P> </P><P>Thanks a lot for your time !!</P>Mon, 21 Mar 2022 10:33:57 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3262421#M139117Kaddrik2022-03-21T10:33:57ZRe: How to find the max value corresponding to a date range
https://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3262455#M139120
<P><LI-USER uid="1124082"></LI-USER> </P><P>Other functions that could be used are MAXIFS or FILTER</P><LI-CODE lang="excel-formula">"Month"
= 1+EOMONTH(UNIQUE(Date),-1)
"Maximum count"
= MAXIFS(Count, Date, UNIQUE(Date))</LI-CODE><P>or, using Lambda functions and FILTER,</P><LI-CODE lang="excel-formula">"Maximum count"
= MAP(UNIQUE(Date),
LAMBDA(d,
MAX(FILTER(Count, Date=d))
)
)</LI-CODE><P> </P>Mon, 21 Mar 2022 11:21:29 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range/m-p/3262455#M139120Peter Bartholomew2022-03-21T11:21:29Z