DMax Criteria problem

[Watco]

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.

 

[joerd]

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.

 

[jghrist]

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:
[tt]
Max of Qty
Part Total
A 10
B 12
C 50
D 25
E 10
Grand Total 50
[/tt]
on a separate sheet.