How Can I SUM a Dynamic Excel Array for Top 10 List, Grouped by Account

Question

I am trying to make a dynamic array featuring a selective Top (whatever…5, 10, 12, etc) Report. Using Index/Sort/Unique/Sequence I am able to get close, but can’t figure out how to SUM the results so that multiple occurrences of an account are grouped into a single quantity total.

I have a table, EXAMPLE1, with six columns (DEALER, ORDER NO, ACCOUNT NAME, YEAR, MONTH, QTY). I’m trying to get my dynamic array, based on four criteria fields (DEALER – Cell I1, YEAR – Cell I2, MONTH – Cell I3, and number of rows for results – Cell I4), to display the results sorted by quantity in descending order, with the quantities totaled by account. Using the formula (which I guess I'm sort of stuck on)

=INDEX(SORT(UNIQUE(FILTER(EXAMPLE1,(EXAMPLE1[DEALER]=$I$1)*(EXAMPLE1[YEAR]=$I$2)*(EXAMPLE1[MONTH]=$I$3),EXAMPLE1)),6,-1),SEQUENCE($I$4),{3,6})

I am able to get the quantity by occurrence and sorted by quantity for the number of rows I want displayed, but some accounts are duplicated if they occur two or more times on the EXAMPLE1 table. So I get

Bob's Boats 74
Marlin Sails Inc    71
Bob's Boats 42
Mike's Lemonade Stand   40
Labyrinth Walkways LLC  32
Direct Mailings LLC 16
Marlin Sails Inc    14

Instead of

Bob's Boats 116
Marlin Sails Inc    85
Mike's Lemonade Stand   40
Labyrinth Walkways LLC  32
Direct Mailings LLC 16

I used alternative formulae,

=SORT(UNIQUE(FILTER(EXAMPLE1[ACCOUNT NAME],(EXAMPLE1[DEALER]=I1)*(EXAMPLE1[YEAR]=I2)*(EXAMPLE1[MONTH]=I3))))

for the account cell, and

=SUMIFS(EXAMPLE1[QTY],EXAMPLE1[DEALER],I1,EXAMPLE1[YEAR],I2,EXAMPLE1[MONTH],I3,EXAMPLE1[ACCOUNT NAME],K16#)

for the quantity cell, which grouped the accounts by quantity totals, but cannot get that array to sort by quantity or provide results based on a desired number of rows.

Bob's Boats 116
Direct Mailings LLC 16
Labyrinth Walkways LLC  32
Marlin Sails Inc    85
Mike's Lemonade Stand   40

Jos Woolley · Accepted Answer · 2023-05-15 16:10:54Z

0

Perhaps try:

=TAKE(
    SORT(
        UNIQUE(
            HSTACK(
                EXAMPLE1[ACCOUNT NAME],
                SUMIFS(
                    EXAMPLE1[QTY],
                    EXAMPLE1[DEALER], I1,
                    EXAMPLE1[YEAR], I2,
                    EXAMPLE1[MONTH], I3,
                    EXAMPLE1[ACCOUNT NAME], EXAMPLE1[ACCOUNT NAME]
                )
            )
        ),2,-1
    ),I4
)

answered May 15, 2023 at 16:10

Jos Woolley

9,1322 gold badges7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

geoff337 Over a year ago

This works great, summing the account totals and grouping them and sorting them by quantity. However, after compiling the account totals that satisfy the criteria, the formula fills out the rest of the rows with the other account names with quantity totals of 0, regardless of the dealer. So with a desired row count of 10, the results look like the below:Bob's Boats 116 Marlin Sails Inc 85 Mike's Lemonade Stand 40 Labyrinth Walkways LLC 32 Direct Mailings LLC 16 Alliance Inc 0 Remote LLC 0 Mom and Pop Inc 0 Truth Inc 0 Rugby LLC 0

Collectives™ on Stack Overflow

How Can I SUM a Dynamic Excel Array for Top 10 List, Grouped by Account

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related