database criteria
 

For the dmax example in excel help it shows the table below. It shows how to
find the max profit of apple and pear trees, or the max profit of apple
trees. Because the criteria requires a column label ("Tree"), is there any
way to find the max profit of just pear trees? I tried
dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column label
is not included in the criteria. Thanks!

A B C D E F
1 Tree Height Age Yield Profit Height
2 Apple 10 <16
3 Pear
4 Tree Height Age Yield Profit
5 Apple 18 20 14 105.00
6 Pear 12 12 10 96.00
7 Cherry 13 14 9 105.00
8 Apple 14 15 10 75.00
9 Pear 9 8 8 76.80
10 Apple 8 9 6 45.00

=sumproduct(max((a5:a10="Pear")*(e5:e10)))

"aspencer27" wrote in message
...
For the dmax example in excel help it shows the table below. It shows how
to
find the max profit of apple and pear trees, or the max profit of apple
trees. Because the criteria requires a column label ("Tree"), is there any
way to find the max profit of just pear trees? I tried
dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column
label
is not included in the criteria. Thanks!

A B C D E F
1 Tree Height Age Yield Profit Height
2 Apple 10 <16
3 Pear
4 Tree Height Age Yield Profit
5 Apple 18 20 14 105.00
6 Pear 12 12 10 96.00
7 Cherry 13 14 9 105.00
8 Apple 14 15 10 75.00
9 Pear 9 8 8 76.80
10 Apple 8 9 6 45.00

Array entered (meaning press Ctrl-Shift-Enter to commit the formula):

=MAX(--(A2:A7="Pear")*E2:E7)

"aspencer27" wrote:

For the dmax example in excel help it shows the table below. It shows how to
find the max profit of apple and pear trees, or the max profit of apple
trees. Because the criteria requires a column label ("Tree"), is there any
way to find the max profit of just pear trees? I tried
dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column label
is not included in the criteria. Thanks!

A B C D E F
1 Tree Height Age Yield Profit Height
2 Apple 10 <16
3 Pear
4 Tree Height Age Yield Profit
5 Apple 18 20 14 105.00
6 Pear 12 12 10 96.00
7 Cherry 13 14 9 105.00
8 Apple 14 15 10 75.00
9 Pear 9 8 8 76.80
10 Apple 8 9 6 45.00

I should have pointed out that the column headings were in row 1 and the
formula assumes the data is in rows 2 thru 7

"aspencer27" wrote:

For the dmax example in excel help it shows the table below. It shows how to
find the max profit of apple and pear trees, or the max profit of apple
trees. Because the criteria requires a column label ("Tree"), is there any
way to find the max profit of just pear trees? I tried
dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column label
is not included in the criteria. Thanks!

A B C D E F
1 Tree Height Age Yield Profit Height
2 Apple 10 <16
3 Pear
4 Tree Height Age Yield Profit
5 Apple 18 20 14 105.00
6 Pear 12 12 10 96.00
7 Cherry 13 14 9 105.00
8 Apple 14 15 10 75.00
9 Pear 9 8 8 76.80
10 Apple 8 9 6 45.00