Aug 17 2021 03:04 PM
I have thousands of IDs, some of which have multiple visits or occurrences. Each occurrence has a value and a date. My objective is to identify the max value out of the repeated occurrences for a specific date range. The code I have written works correctly for the majority of my IDs, however some IDs have issues. For example, in my file, for column P, I want the max value for each ID from between the dates 1/1/16 and 12/31/17. This is what I get for the majority of IDs, except as you see with ID 100003, it is providing me with a date from 2015, when you can see from column C that ID has visits in 2016 and 2017. The correct value that should pop up in cell P5 is 6.2, but the associated date in Q5 should be 3/25/16 because that date falls between 1/1/16 and 12/31/17.
Any help would be greatly appreciated.
Aug 17 2021 03:16 PM
SolutionSee the attached version.
Aug 17 2021 08:56 PM
Aug 18 2021 03:09 AM
Could the RESULT_VALUE column contain invalid entries, for example text values? Try this formula in H2, also confirmed with Ctrl+Shift+Enter:
=MAX(IF(($A$2:$A$55665=G2)*($C$2:$C$55665>=DATE(2016,1,1))*($C$2:$C$55665<=DATE(2017,12,31)),$E$2:$E$55665))
Aug 17 2021 03:16 PM
Solution