Maxif without array

[ShanaVT]

I am trying to create a maxif statement without using an array. When I use this formula without an array =MAX(IF(AND($A$1:$A$5=A1, $B$1:$B$5=B1),($C$1:$C$5)) it works for the range of rows 1 to 5 but since I want to be able to drag this formula down and not redo it for each section and when rows are added I changed it to =MAX(IF(AND($A:$A=A1, $B:$B$B1),($C:$C)). After the change it no longer returns the correct values. Any ideas why or how to fix it without using an array?

 

[Jonmo1]

Why the opposition to array formulas?

I'm not sure your posted formula actually works the way you think it does.

It should be array entered
=MAX(IF(($A$1:$A$5=A1)*($B$1:$B$5=B1),$C$1:$C$5))

 

[63falcondude]

I think you have to use an array formula unless you have Excel 2016. Not sure why it worked for you for rows 1-5 without an array.

 

[ShanaVT]

The file is already quite large and the array significantly impacts calculation time since it will be repeated for 1000+ rows. Below is the example data set where column D reflects the desired solution. I also don't want to lock the cells for each range (A, B, C, D) - in other words I need to be able to drag the formula all the way down column D.

A	1	1	5
A	2	2	5
A	3	3	5
A	4	4	5
A	5	5	5
B	1	6	10
B	2	7	10
B	3	8	10
B	4	9	10
B	5	10	10
C	1	11	15
C	2	12	15
C	3	13	15
C	4	14	15
C	5	15	15
D	1	16	20
D	2	17	20
D	3	18	20
D	4	19	20
D	5	20	20

<tbody>
</tbody>

 

[Jonmo1]

the array significantly impacts calculation time since it will be repeated for 1000+ rows.

1000+ rows of formulas already impacts calculation time, whether they are arrays or not.
What matters is the 'efficiency' of the formula.
Array DOES NOT automatically mean Not Efficient.
The same Logic/Calcluations have to occur whether it's an array or non-array formula.

Anyway, off the soapbox.


So assuming this formula does deliver the desired result for the first row
=MAX(IF(($A$1:$A$5=A1)*($B$1:$B$5=B1),$C$1:$C$5))

Can you manually adjust it for a few rows as you would like it to happen automatically as dragged down?

 

[ghrain22]

I don't think an array formula would cause too much of a performance issue. I would imagine a column reference could potentially slow you down, have you considered using a dynamic named range?

 

[Jonmo1]

Which version of Excel are you using?
XL2010+ has the Aggregate function which should work

=AGGREGATE(14,6,($A$1:$A$5=A1)*($B$1:$B$5=B1)*$C$1:$C$5,1)