get the max based on a criteria but the criteria is not the format i want

[uimhirADo]

Hi there
See the attached which hopeully explains if better...
How do I get the max of ValueA, using columnA, for the month of June and put it in column G3
There is currently july and august dates in there as well how do I do the same for these.
Assume that hourly data will be appended to this so in future I will want to get July values…etc
I just want to use formulas, as I know away to to it using pivots
I already have the max time that the first max occurred in ge:i2

Thanks

[XOR LX]

Hi,

What do you wish to do in the event of more than one value matching the max?

This array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) will give you the desired results for June:

=INDEX($A$2:$A$1416,MATCH(1,(($B$2:$B$1416=MAX(IF(MONTH($A$2:$A$1416)=6,$B$2:$B$1416)))*(MONTH($A$2:$A$1416)=6)),0))

Amend the 6 in the MONTH($A$2:$A$1416)=6 parts to 7, 8, for July, August, etc. We can make this dynamic quite easily as well if you intend to repeat this formula for many months.

What about those duplicates?

Regards

[uimhirADo]

Hi XOR LX,

What i want is( and the same for July and August...

Please Login or Register  to view this content.

What you gave me does

Please Login or Register  to view this content.

I think yours gets me the max date out of column A, but I haven't really dived into your formula. What I want is the max out of column B,C and D. 100 in this case.

On the duplicates: the data I will be dealing with generally will not have a duplicate in the same month. But that does not mean it will never happen. So whilst I am aware of this issue I don't really need to address it. But I would be interested to hear your thoughts on how you handle it. Do you put it in the same Cell or create another cell for it??

Thanks

[Ace_XL]

Change XOR LX's formula to refer to the correct column (B,C and D)

=INDEX(B$2:B$1416,MATCH(1,((B$2:B$1416=MAX(IF(MONTH($A$2:$A$1416)=$K3,B$2:B$1416)))*(MONTH($A$2:$A$1416)=$K3)),0))

Ctrl+Shift+Enter