×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
To Your Posts
• Keyword Search
• One-Click Access To Your
Favorite Forums
• Automated Signatures
On Your Posts
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DMax Criteria problem

 Forum Search FAQs Links MVPs

DMax Criteria problem

(OP)
Part#     QTY    Max
A          10    10
A           8    10
B          12    12
C           6    50
C           8    50
C           9    50
C          50    50
D          25    25
D          15    25
E          10    10
E           6    10
E           3    10
Do you know how to get the DMax function to calculate the above Max column of data?  I have Part# and Qty, but I need the worksheet to calculate Max.  The tricky part is that the part number list is very long, and it changes when I refresh the imported list.

RE: DMax Criteria problem

I have the same problem as you to get DMAX to work. What you could use as a workaround: in the Max column, write
=MAX($B$2:$B$13*($A$2:$A$13=A2))
and enter it as  an array formula (press Ctrl-Enter instead of Enter after you have typed the formula. The formula will display as {=MAX($B$2:$B$13*($A$2:$A$13=A2))}. Array formulas tend to get slow in the case of large databases, however

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: DMax Criteria problem

Do you need the maximum to be in the same table?  You can easily get a second table with just the maximums by using a pivot table.  Put the pointer anywhere in the database, Select Data|Pivot Table in the menu, Next, Next, Layout, drag Part to the Row area and Qty to the Data area (it will become Sum of Qty, double click to change to Max), OK, then Finish.  You will get:

Max of Qty
Part        Total
A            10
B            12
C            50
D            25
E            10
Grand Total  50

on a separate sheet.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.