Need function for Max/Count

[jweisman]

I have data in the following format:

Item Total
test 1
test 2
test1 5
test1 4
test1 6
test2 7
test3 8
test4 6
test5 3

I want to be able to find the item with the highest total. I know that this would be very easy with a pivot table, but I want to use a function. So I need a formula that sums up the total for each item and returns the max. For this example, the result would be test1 = 15.

Thanks,
Jeff

 

[SkipVought]

Hi,

Check out Data/Pivot Table

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[mintjulep]

How can you have more than one result for the same test?

 

[byundt]

jweisman,
Try the following two array formulas to find the maximum sum and Item responsible for it.
=MAX(SUMIF(A2:A10,A2:A10,B2:B10))
=INDEX(A2:A10,MATCH(MAX(SUMIF(A2:A10,A2:A10,B2:B10)),SUMIF(A2:A10,A2:A10,B2:B10),0))
These formulas assume that your items are listed in A2:A10, with the Totals in column B.

Array formulas need to be entered in a special way: hold the Control and Shift keys down, then press Enter. Excel should respond by adding curly braces { } surrounding the formula. If it doesn't, then select the cell, click in the formula bar and CTRL + Shift + Enter.

Sample workbook showing how it all works:

http://home.mchsi.com/~byundt/FindMaximumSumTT1218511.xls

Brad