+ Reply to Thread
Results 1 to 4 of 4

Need to calculate sum of a range of MAX values

  1. #1
    Registered User
    Join Date
    05-12-2021
    Location
    Canada
    MS-Off Ver
    16.0
    Posts
    4

    Need to calculate sum of a range of MAX values

    In A1:A5 I have the following values:

    12 9 6 11 10

    I am trying to write a formula to calculate the sum of A1:A5, but apply MAX(ref, 10) to each value before calculating the sum. The result should be 45, calculated as 10+9+6+10+10. Is this possible to somehow include an array with a MAX formula? Ideally I will not use helper columns.

    Thank you.
    Last edited by mom_; 05-26-2021 at 02:39 PM.

  2. #2
    Forum Contributor
    Join Date
    05-18-2021
    Location
    London, Ontario
    MS-Off Ver
    365
    Posts
    115

    Re: Need to calculate sum of a range of MAX values

    =sum(if(a1:a5>10,10,a1:a5))

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Need to calculate sum of a range of MAX values

    Try

    =SUMIF(A1:A5,">=10")

  4. #4
    Registered User
    Join Date
    05-12-2021
    Location
    Canada
    MS-Off Ver
    16.0
    Posts
    4

    Re: Need to calculate sum of a range of MAX values

    Thank you dohvakin123. I did have to use IF functions instead of MAX. Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate based on a range of values
    By JennOlsen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-11-2018, 12:20 PM
  2. Need to calculate average if values fall into EITHER range.
    By daxplicitazn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-25-2013, 12:37 PM
  3. Calculate the Range based on certain values
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 05-14-2013, 08:09 AM
  4. Calculate the maximum TEXT values in a range, but exclude one value?
    By kpratico in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2010, 04:00 PM
  5. Calculate if Range falls between two values
    By Michaniker in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2010, 03:35 PM
  6. Calculate range based on cell values
    By jongleurjon in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-09-2009, 07:21 AM
  7. Formula to calculate highest values in a range
    By Dabooj in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-09-2008, 10:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1