# Need a alternate solution for Maxifs

#### Divakarkumar

##### New Member

Hi Team,

I have used Maxifs (=MAXIFS(\$B\$2:\$B\$13,\$A\$2:\$A\$13,A2)=B2) to find latest version of quote number(False/ True) but my bad luck the same Maxifs forumal is not available in my Virtual Machine due to excel version issue.

Examples : I have 10 number repeated in A columns 5 times and B column Version like 12345 in the same row and I have to get 4 false and 1 true which is latest one (10 and 5) , I have attached image for better understate.

False : indicate old version
True : Indicate new version

Great if some one provide alternate option to built same logic.

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Dave Patton

##### Well-known Member
You did not post an example that we can use; see XL2BB.

T202002a.xlsm
BCDE
1Max based on criteria
2A200FALSEA
3B220FALSE1,320
4C240FALSE1,320
5A260FALSE
6B280FALSE
7C300FALSE
8A1320TRUE
9B340TRUE
10C360TRUE
Installations
Cell Formulas
RangeFormula
E3E3=MAX((Installations!B2:B455=E2)*Installations!C2:C455)
E4E4=AGGREGATE(14,6,Installations!C\$2:C\$455/(Installations!B\$2:B\$455=E2),1)
D2:D10D2=AGGREGATE(14,6,Installations!C\$2:C\$455/(Installations!B\$2:B\$455=B2),1)=C2
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Divakarkumar

##### New Member
You did not post an example that we can use; see XL2BB.
========================

Hi Sir,

Your result is coming as i expected but i'm not able to understand the formula which you have used, could you please use the same formula in below data set in C column, need false or true result based on latest quote.

and I really appreciate your help

 Quote Numbers PVR Version Latest PVR Version 10 1 11 2 10 3 11 4 14 5 15 6 14 7 15 8 18 9 10 5 18 11 19 12

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Adapting the last formula suggested in post 3 to your data layout does exactly as the MAXIFS formula.

20 07 31.xlsm
ABCD
1Quote NumbersPVR VersionLatest PVR Version
2101FALSEFALSE
3112FALSEFALSE
4103FALSEFALSE
5114TRUETRUE
6145FALSEFALSE
7156FALSEFALSE
8147TRUETRUE
9158TRUETRUE
10189FALSEFALSE
11105TRUETRUE
121811TRUETRUE
131912TRUETRUE
MAXIFS Replacement
Cell Formulas
RangeFormula
C2:C13C2=MAXIFS(\$B\$2:\$B\$13,\$A\$2:\$A\$13,A2)=B2
D2:D13D2=AGGREGATE(14,6,B\$2:B\$13/(A\$2:A\$13=A2),1)=B2

#### Divakarkumar

##### New Member
Thanks you so much for your help Sir, really I appreciate your help

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Dave did the work though - I just tweaked the ranges.

Replies
5
Views
560
Replies
0
Views
129
Replies
9
Views
132
Replies
0
Views
115
Replies
0
Views
84

1,171,650
Messages
5,876,684
Members
433,205
Latest member
jabin1991

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back