SOLVED

Conversion of a MAXIFS function into MAX with multiple IFs

Occasional Contributor

Hello all,

 

hope you're doing good.

So...here i am again, still not good at Excel.. sorry

 

some time ago i asked the question : How to find the maximum value corresponding to a date range.... knowing that on a sheet i have dates per week and the MAX value has to be reported to a month.

 

https://techcommunity.microsoft.com/t5/excel/how-to-find-the-max-value-corresponding-to-a-date-range... 

 

@Starrysky1988  was really nice and gave me the solution...thanks again...

 

but now i'm figuring out i have another problem. Some people are still working on an old version of Office so MAXIFS is not working. this means i have to convert the formula @Starrysky1988  gave me into a MAX with multiple IFs

 

I get something like this but it returns value 0 (meaning it's not returning the maximum value):

 

MAX(IF('Sheet1'!A:A>='Sheet2'!G18;IF('Sheet1'!A:A<=EOMONTH('Sheet2'!G18;0);'Sheet1'!CA:CA)))

 

where Sheet1 is where i have my dates by week (column A) and the column with the corresponding values for each week ( column CA)

and Sheet2 has the date per month (column G)

 

Would someone please be able to tell me what i\m converting wrong ?

 

Thanks a lot and regards !

 

2 Replies
best response confirmed by Kaddrik (Occasional Contributor)
Solution

@Kaddrik 

Confirm the formula with Ctrl+Shift+Enter each time you edit it.

Hi @Hans Vogelaar ,

Thanks a lot for this !
Yes it works