SOLVED

Conversion of a MAXIFS function into MAX with multiple IFs

%3CLINGO-SUB%20id%3D%22lingo-sub-3363018%22%20slang%3D%22en-US%22%3EConversion%20of%20a%20MAXIFS%20function%20into%20MAX%20with%20multiple%20IFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363018%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20you're%20doing%20good.%3C%2FP%3E%3CP%3ESo...here%20i%20am%20again%2C%20still%20not%20good%20at%20Excel..%20sorry%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esome%20time%20ago%20i%20asked%20the%20question%20%3A%20How%20to%20find%20the%20maximum%20value%20corresponding%20to%20a%20date%20range....%20knowing%20that%20on%20a%20sheet%20i%20have%20dates%20per%20week%20and%20the%20MAX%20value%20has%20to%20be%20reported%20to%20a%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20title%3D%22Previous%20question%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fhow-to-find-the-max-value-corresponding-to-a-date-range%2Fm-p%2F3260850%2Femcs_t%2FS2h8ZW1haWx8dG9waWNfc3Vic2NyaXB0aW9ufEwwVzkyRzlLRTJCR1I1fDMyNjA4NTB8U1VCU0NSSVBUSU9OU3xoSw%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fhow-to-find-the-max-value-corresponding-to-a-date-range%2Fm-p%2F3260850%2Femcs_t%2FS2h8ZW1haWx8dG9waWNfc3Vic2NyaXB0aW9ufEwwVzkyRzlLRTJCR1I1fDMyNjA4NTB8U1VCU0NSSVBUSU9OU3xoSw%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319311%22%20target%3D%22_blank%22%3E%40Starrysky1988%3C%2FA%3E%26nbsp%3B%20was%20really%20nice%20and%20gave%20me%20the%20solution...thanks%20again...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20now%20i'm%20figuring%20out%20i%20have%20another%20problem.%20Some%20people%20are%20still%20working%20on%20an%20old%20version%20of%20Office%20so%20MAXIFS%20is%20not%20working.%20this%20means%20i%20have%20to%20convert%20the%20formula%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319311%22%20target%3D%22_blank%22%3E%40Starrysky1988%3C%2FA%3E%26nbsp%3B%20gave%20me%20into%20a%20MAX%20with%20multiple%20IFs%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20something%20like%20this%20but%20it%20returns%20value%200%20(meaning%20it's%20not%20returning%20the%20maximum%20value)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMAX(IF('Sheet1'!A%3AA%26gt%3B%3D'Sheet2'!G18%3BIF('Sheet1'!A%3AA%26lt%3B%3DEOMONTH('Sheet2'!G18%3B0)%3B'Sheet1'!CA%3ACA)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20Sheet1%20is%20where%20i%20have%20my%20dates%20by%20week%20(column%20A)%20and%20the%20column%20with%20the%20corresponding%20values%20for%20each%20week%20(%20column%20CA)%3C%2FP%3E%3CP%3Eand%20Sheet2%20has%20the%20date%20per%20month%20(column%20G)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20someone%20please%20be%20able%20to%20tell%20me%20what%20i%5Cm%20converting%20wrong%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20and%20regards%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3363018%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363247%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20of%20a%20MAXIFS%20function%20into%20MAX%20with%20multiple%20IFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124082%22%20target%3D%22_blank%22%3E%40Kaddrik%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConfirm%20the%20formula%20with%20Ctrl%2BShift%2BEnter%20each%20time%20you%20edit%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363429%22%20slang%3D%22en-US%22%3ERe%3A%20Conversion%20of%20a%20MAXIFS%20function%20into%20MAX%20with%20multiple%20IFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363429%22%20slang%3D%22en-US%22%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20a%20lot%20for%20this%20!%3CBR%20%2F%3EYes%20it%20works%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
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