CODEWITHSUNDEEP
excelexcel-formulasummax
Gravitate
Gravitate

Reputation: 3064

How do you sum the single max values of multiple groups?

I have a data table which, among other things, contains a list of Values, each is assigned a Group ID.

I would like to sum the max value of each group.

I can do this easily by using a helper table (with MAXIFS) as can be seen below. However, for various reasons, I would like to avoid any helper columns/tables and produce the result from a single, non-CSE, formula.

enter image description here

How can this be done?

Upvotes: 3

Views: 2009

Answers (4)

EEM
EEM

Reputation: 6659

You could also try this:

= SUMPRODUCT( 
 MAXIFS( $B$2:$B$14, $A$2:$A$14, $A$2:$A$14 )
 / COUNTIFS( $A$2:$A$14, $A$2:$A$14 ) )

Explanation: (provided now as previously I was travelling).

MAXIFS: Returns the MAX value for each Group.
COUNTIFS: Returns the count of each Group.
SUMPRODUCT: Returns the addition of the division of each Group's Max value by it's Count.

enter image description here

Upvotes: 5

cte6
cte6

Reputation: 637

Try this:

=SUM(MAXIFS(B3:B14,A3:A14,{1,2,3,4}))

enter image description here

Upvotes: 1

Gravitate
Gravitate

Reputation: 3064

Dynamic Array Functions

IF you have access to the new dynamic array functions, @MichaelWycisk has a much simpler solution (https://stackoverflow.com/a/59306217/1473412)

Sadly I do not.

SUMPRODUCT

I have managed to do it using SUMPRODUCT:

=SUMPRODUCT(
    (
        $B$3:$B$14
        *
        ($B$3:$B$14=MAXIFS($B$3:$B$14,$A$3:$A$14,$A$3:$A$14))
    )
    /
    COUNTIFS($B$3:$B$14,MAXIFS($B$3:$B$14,$A$3:$A$14,$A$3:$A$14),$A$3:$A$14,$A$3:$A$14)
)

enter image description here

The way it works is to multiply the values by whether or not it is the max value for the group.
Then it divides that by the count of values which are the max value for the group.

Upvotes: 7

Michael Wycisk
Michael Wycisk

Reputation: 1695

I understand you do not want to have array formulas. But if you would be fine with the new dynamic arrays (no CONTROL + SHIFT + ENTER needed), you might use this formula:

=SUM(MAXIFS(B3:B14,A3:A14,UNIQUE(A3:A14)))

Of course, this will only work if the UNIQUE function is already available to you.

Upvotes: 4

Related Questions