Using Max IF functions

W

wiebe

New Member
Joined
Apr 29, 2015
Messages
6
Dear all,

Having some difficulty with the max if formula.... example of my data.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]401580[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]385780[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]103500[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]103500[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]95220[/TD]
[/TR]
</tbody>[/TABLE]

I would like to find the max value if column A is "A".

I have the following coding but it returns "#NAME?"

Sub max_if()

Dim columnA As Range
Dim columnB As Range
Set columnA = [E:E]
Set columnB = [F:F]


[E1] = Evaluate("=MAX(IF(columnA=A,columnB))")


End Sub

Any help would be greatly appreciated!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
To expand on Brian's solution, here is how you would insert an array formula into the sheet using VBA:

Code: 
Sub max_if()

Range("H1").FormulaArray = "=MAX(IF(E:E=""A"",F:F))"

End Sub

Though I would avoid using entire column references if you're using an array formula.
 
Upvote 0
wiebe said:
Dear all,

Having some difficulty with the max if formula.... example of my data.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]401580[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]385780[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]103500[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]103500[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]95220[/TD]
[/TR]
</tbody>[/TABLE]

I would like to find the max value if column A is "A".

I have the following coding but it returns "#NAME?"

Sub max_if()

Dim columnA As Range
Dim columnB As Range
Set columnA = [E:E]
Set columnB = [F:F]


[E1] = Evaluate("=MAX(IF(columnA=A,columnB))")


End Sub

Any help would be greatly appreciated!
Click to expand...

Try this for the evaluate MAX-IF syntax

[E1] = Evaluate("MAX(IF(" & columnA.Address & "=""A""," & columnB.Address & "))")
 
Upvote 0
AlphaFrog said:
Try this for the evaluate MAX-IF syntax

[E1] = Evaluate("MAX(IF(" & columnA.Address & "=""A""," & columnB.Address & "))")
Click to expand...

Thank you!!! that worked like a charm! I have one more question about the conditional formula...
If I have a 3rd column and want to display the information along with the max value, what can I do? Or is that not possible? Thank you again.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]401580[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]385780[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]103500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]103500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]95220[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Code: 
[color=darkblue]Sub[/color] max_if()
    
[color=darkblue]Dim[/color] columnA [color=darkblue]As[/color] Range
[color=darkblue]Dim[/color] columnB [color=darkblue]As[/color] Range
[color=darkblue]Dim[/color] columnC [color=darkblue]As[/color] Range
    
[color=darkblue]Set[/color] columnA = [E1:E100]
[color=darkblue]Set[/color] columnB = [F1:F100]
[color=darkblue]Set[/color] columnC = [G1:G100]
    
[E1] = Evaluate("MAX(IF(" & columnA.Address & "=""A""," & columnB.Address & "))")
[E2] = Evaluate("Index(" & columnC.Address & ",Match(1,(" & columnA.Address & "=""A"")*(" & columnB.Address & "=E1),0))")
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
AlphaFrog said:
Code: 
[COLOR=darkblue]Sub[/COLOR] max_if()
    
[COLOR=darkblue]Dim[/COLOR] columnA [COLOR=darkblue]As[/COLOR] Range
[COLOR=darkblue]Dim[/COLOR] columnB [COLOR=darkblue]As[/COLOR] Range
[COLOR=darkblue]Dim[/COLOR] columnC [COLOR=darkblue]As[/COLOR] Range
    
[COLOR=darkblue]Set[/COLOR] columnA = [E1:E100]
[COLOR=darkblue]Set[/COLOR] columnB = [F1:F100]
[COLOR=darkblue]Set[/COLOR] columnC = [G1:G100]
    
[E1] = Evaluate("MAX(IF(" & columnA.Address & "=""A""," & columnB.Address & "))")
[E2] = Evaluate("Index(" & columnC.Address & ",Match(1,(" & columnA.Address & "=""A"")*(" & columnB.Address & "=E1),0))")
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...

Thanks AlphaFrog,

I made a little change to reflect the actual layout of my data but received "#N/A". Same results even when I enter it in a blank page with the exact coding as above. Any suggestion? ><
 
Upvote 0
wiebe said:
Thanks AlphaFrog,

I made a little change to reflect the actual layout of my data but received "#N/A". Same results even when I enter it in a blank page with the exact coding as above. Any suggestion? ><
Click to expand...

Don't know what to tell you. It works for me.
Excel Workbook
EFG
110
2Pear
3
4
5A1Apple
6A10Pear
7A2Banana
8B100Orange
9B10Grape
Sheet


Code: 
[color=darkblue]Sub[/color] max_if()
    
[color=darkblue]Dim[/color] columnA [color=darkblue]As[/color] Range
[color=darkblue]Dim[/color] columnB [color=darkblue]As[/color] Range
[color=darkblue]Dim[/color] columnC [color=darkblue]As[/color] Range
    
[color=darkblue]Set[/color] columnA = [E5:E10]
[color=darkblue]Set[/color] columnB = [F5:F10]
[color=darkblue]Set[/color] columnC = [G5:G10]
    
[E1] = Evaluate("MAX(IF(" & columnA.Address & "=""A""," & columnB.Address & "))")
[E2] = Evaluate("Index(" & columnC.Address & ",Match(1,(" & columnA.Address & "=""A"")*(" & columnB.Address & "=E1),0))")
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Did both error?
 
Upvote 0
AlphaFrog said:
Don't know what to tell you. It works for me.

EFG
Pear
AApple
ABanana
BOrange
BGrape

<colgroup><col style="width:30px; "><col style="width:42px;"><col style="width:28px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ffff99"]A[/TD]
[TD="bgcolor: #ffff99, align: right"]10[/TD]
[TD="bgcolor: #ffff99"]Pear[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]10[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Code: 
[COLOR=darkblue]Sub[/COLOR] max_if()
    
[COLOR=darkblue]Dim[/COLOR] columnA [COLOR=darkblue]As[/COLOR] Range
[COLOR=darkblue]Dim[/COLOR] columnB [COLOR=darkblue]As[/COLOR] Range
[COLOR=darkblue]Dim[/COLOR] columnC [COLOR=darkblue]As[/COLOR] Range
    
[COLOR=darkblue]Set[/COLOR] columnA = [E5:E10]
[COLOR=darkblue]Set[/COLOR] columnB = [F5:F10]
[COLOR=darkblue]Set[/COLOR] columnC = [G5:G10]
    
[E1] = Evaluate("MAX(IF(" & columnA.Address & "=""A""," & columnB.Address & "))")
[E2] = Evaluate("Index(" & columnC.Address & ",Match(1,(" & columnA.Address & "=""A"")*(" & columnB.Address & "=E1),0))")
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Did both error?
Click to expand...

I finally found out the problem... I did not change the second line... I changed the E1 from (" & columnB.Address & "=E1),0))") to the corresponding cell I want in my worksheet and now it works!!! Thank you for your assistance!!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Find Max Value by Group VBA
Replies
3
Views
898
DanteAmor
DanteAmor
ajjava
  • Question Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
2K
ajjava
ajjava
G
  • Question Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
389
GK039
G
M
  • Question Question
VBA - Returning Max value using array VBA
Replies
8
Views
2K
rlv01
R
S
  • Solved
Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2
Replies
5
Views
559
Alex Blakenburg
A

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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