MAX IF array inside an IF formula??

I think I have managed to add some more comments to my original post along with an attachment.

Hello and welcome new person

Just enter it all as one single array formula, something like

{=IF(A1<0,MAX(IF(...)),MAX(IF(...)))}

You were close, 1 bracket in the wrong place, and you didn't array confirm your formulas in H5:H16.

H5 should be

=IF($H$2>0,MAX(IF('Data Import'!K:K=Summary!A5,IF('Data Import'!E:E=Summary!$H$2,'Data Import'!AB:AB))),MAX(IF('Data Import'!K:K=Summary!A5,'Data Import'!AB:AB)))

Remember to confirm the array formula with Shift Ctrl Enter before filling down.

Haha no way just 1 bracket. - I can't believe I forgot to CSE. Thank you works perfectly now

so I realised that the above formula did not do exactly what I needed as I missed some date cells out so I need to ensure the calculation is based only between 2 dates which I have now included. this is the VBA code but it is not working and I get the debug error every time. I am sure I must be a bracket missing or int he wrong place. can anyone take a quick look and provide any solutions?

Selection.FormulaArray = _
"=IF(R2C8>0,MAX(IF('Data Import'!C[3]=Summary!RC[-7],IF('Data Import'!C[2]>=Summary!R2C4,IF('Data Import'!C[2]<=Summary!R2C5,IF('Data Import'!C[-3]=Summary!R2C8,'Data Import'!C[20]))))),MAX(IF('Data Import'!C[3]=Summary!RC[-7],IF('Data Import'!C[2]" >= "Summary!R2C4,IF('Data Import'!C[2]" <= "Summary!R2C5,'Data Import'!C[20]))))"

Not sure about brackets, but you have some stray double inverted commas which will be causing problems.
Also, I don't think that FormulaArray will accept R1C1 cell references, you might have to enter it as a regular R1C1 formula, then force that to an array afterwards.

Untested

Selection.FormulaR1C1 = _
 "=IF(R2C8>0,MAX(IF('Data Import'!C[3]=Summary!RC[-7],IF('Data Import'!C[2]>=Summary!R2C4,IF('Data Import'!C[2]<=Summary!R2C5,IF('Data Import'!C[-3]=Summary!R2C8,'Data Import'!C[20]))))),MAX(IF('Data Import'!C[3]=Summary!RC[-7],IF('Data Import'!C[2] >= Summary!R2C4,IF('Data Import'!C[2] <= Summary!R2C5,'Data Import'!C[20]))))"
Selection.FormulaArray = Selection.Formula

Originally Posted by jason.b75

Not sure about brackets, but you have some stray double inverted commas which will be causing problems.
Also, I don't think that FormulaArray will accept R1C1 cell references, you might have to enter it as a regular R1C1 formula, then force that to an array afterwards.

Untested

Selection.FormulaR1C1 = _
 "=IF(R2C8>0,MAX(IF('Data Import'!C[3]=Summary!RC[-7],IF('Data Import'!C[2]>=Summary!R2C4,IF('Data Import'!C[2]<=Summary!R2C5,IF('Data Import'!C[-3]=Summary!R2C8,'Data Import'!C[20]))))),MAX(IF('Data Import'!C[3]=Summary!RC[-7],IF('Data Import'!C[2] >= Summary!R2C4,IF('Data Import'!C[2] <= Summary!R2C5,'Data Import'!C[20]))))"
Selection.FormulaArray = Selection.Formula

I have removed the apostrophes as stated as realised after posting. Still not working. I now get a message wgich i beleive relates to the lengrh od my formula. I dont think i can have a Formula more than 255 digits for an array in vba so now i am havig to rethink.

Originally Posted by mtleigh

I dont think i can have a Formula more than 255 digits for an array in vba so now i am havig to rethink.

Same formula, with a different approach to the logic. Consider reducing the range sizes as well, using entire columns in array formulas is extremely inefficient.

Selection.FormulaArray = _
 "=MAX(IF('Data Import'!C[3]=Summary!RC[-7],IF('Data Import'!C[2]>=Summary!R2C4,IF('Data Import'!C[2]<=Summary!R2C5,IF(R2C8>0,IF('Data Import'!C[-3]=Summary!R2C8,'Data Import'!C[20]),'Data Import'!C[20])))))"

One point to note, I was wrong with my earlier comment, you can use R1C1 references with FormulaArray, just tested the above in excel and it works as expected.