SOLVED

Conversion of a MAXIFS function into MAX with multiple IFs

Occasional Contributor

Conversion of a MAXIFS function into MAX with multiple IFs

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.

@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

Re: Conversion of a MAXIFS function into MAX with multiple IFs

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

Re: Conversion of a MAXIFS function into MAX with multiple IFs

Hi @Hans Vogelaar ,

Thanks a lot for this !
Yes it works