Share via

Excel Sum top 3 values

Anonymous
2022-12-02T14:07:12+00:00

I'd like to sum the top 3 values of a column, there are multiple values for each,

I;ve used Sumif ( range, { 15,14,13} ) which I can then wrap in a sum, but would like to use

SUMIFS with an OR ,so presume "+" to join them,

I've tried sumproduct but this does not give the correct result, 

      SUMPRODUCT((Table1[Unit]=15)+(Table1[Unit]=13)+(Table1[Unit]=14))

I'd also like to avoid adding multuple sumuf statement to together.

Richard.

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

riny 20,870 Reputation points Volunteer Moderator
2022-12-02T15:19:59+00:00

It doesn't seem to be a very meaningful formula but perhaps this does what you describe.

=SUMIFS(sum_range,criteria_range,">=13",criteria_range,"<=15")

Was this answer helpful?

1 person found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-12-02T20:19:34+00:00

    I'm working on 

                SUMIF(F4:F14,"&gt;="&LARGE(F4:F14,3),F4:F14)

    But I need to make allownace for duplicates, which Large does not it is do-able,

    but i'm having a rest.

    Was this answer helpful?

    0 comments
  2. Lz365 38,191 Reputation points Volunteer Moderator
    2022-12-02T17:58:58+00:00

    Hi Richard

    Let's assume we want to sum the values in C3:C10 for the 3 largest values in B3:B10

    With Excel 2021 or 365, in F2:

    =SUM( SUMIF(B3:B10, LARGE(B3:B10, SEQUENCE(3)), C3:C10) )

    Other Excel versions, in F2:

    =SUM( SUMIF(B3:B10, LARGE(B3:B10, {1,2,3}), C3:C10) )

    And confirm the formula with Ctrl+Shift+Enter

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-12-02T17:56:12+00:00

    Hi there

    Please try this formula

    =SUMIFS(Table1[Unit],">=13",Table1[Unit],"<=15")

    Regards

    Jeovany

    Was this answer helpful?

    0 comments
  4. Anonymous
    2022-12-02T16:16:52+00:00

    Yes, but I was more interested in how to get an OR syntax into the sumifs in this situation,

    also, as with mine using the spilled range in the first example
    SUM ( SUMIF( range ,{ criteria} ) )

    the criteria are hard coded and I'd like them to be dynamic.

    RD

    Was this answer helpful?

    0 comments