Finding largest value

L

Leonidas75

Board Regular
Joined
May 12, 2015
Messages
52
Hello,

I am trying to find the largest value based on more than 3 criteria.

I have 2016 edition so MAXIFS function is not available.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Morning,

You would need to use MAX and IF functions in an array formula.

For example:

{=MAX(IF(A2:A14=F2,IF(B2:B14=G2,IF(C2:C14=H2,D2:D14))))}

1580371915215.png


For some reason my array isn't working but I'm sure this is what the formula should look like.
 
Last edited:
Upvote 0
Another option
ZFluff.xlsm
ABCDEFGHI
1CountyDistrictWardNorthing
2HertfordshireDacorumWatling215981HertfordshireSt AlbansAshley207598
3HertfordshireSt AlbansAshley206823HertfordshireSt AlbansHarpenden North215786
4HertfordshireSt AlbansAshley207044
5HertfordshireSt AlbansAshley207598
6HertfordshireSt AlbansBatchwood208129
7HertfordshireSt AlbansClarence207879
8HertfordshireSt AlbansColney Heath207227
9HertfordshireSt AlbansHarpenden East215160
10HertfordshireSt AlbansHarpenden East214092
11HertfordshireSt AlbansHarpenden North215786
12HertfordshireSt AlbansHarpenden North213215
13HertfordshireSt AlbansHarpenden North212930
14HertfordshireSt AlbansHarpenden North215051
15HertfordshireSt AlbansLondon Colney203589
16HertfordshireSt AlbansMarshalswick North209258
17HertfordshireSt AlbansPark Street204883
18HertfordshireSt AlbansRedbourn209483
19HertfordshireSt AlbansRedbourn212067
20HertfordshireSt AlbansRedbourn216370
21HertfordshireSt AlbansSandridge209280
22HertfordshireSt AlbansSopwell206096
23HertfordshireSt AlbansSt Peters206888
24HertfordshireSt AlbansSt Stephen202756
25HertfordshireSt AlbansVerulam206399
26HertfordshireSt AlbansWheathampstead212503
27HertfordshireSt AlbansWheathampstead213375
28HertfordshireWelwyn HatfieldHatfield Central209366
29HertfordshireWelwyn HatfieldHatfield Central208775
30HertfordshireWelwyn HatfieldHatfield Villages208470
31HertfordshireWelwyn HatfieldWelham Green and Hatfield South206708
32HertfordshireWelwyn HatfieldWelwyn West217390
Main
Cell Formulas
RangeFormula
I2:I3I2=AGGREGATE(14,6,$D$2:$D$32/(($A$2:$A$32=F2)*($B$2:$B$32=G2)*($C$2:$C$32=H2)),1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Delete duplicate rows while keeping the largest value and smallest value corresponding to the same name
Replies
4
Views
149
sofas
S
VinceF
  • Solved
Help with Formula
Replies
3
Views
66
Joe4
Joe4
S
  • Question
Organization Chart Formula
Replies
3
Views
61
ShogunStealth
S
D
  • Solved
Identify top three maximum unique values in a range using VBA
Replies
2
Views
430
DonEB
D
M
  • Question
Maximum window size excel
Replies
1
Views
89
jkpieterse
jkpieterse

Forum statistics

Threads
1,214,923
Messages
6,122,286
Members
449,076
Latest member
kenyanscott

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top