+ Reply to Thread
Results 1 to 4 of 4

Summation Formula of Several Values with a Ceiling

  1. #1
    Registered User
    Join Date
    06-08-2013
    Location
    Doha, Qatar
    MS-Off Ver
    Excel 2010
    Posts
    2

    Summation Formula of Several Values with a Ceiling

    I want to have a summation of data cells with different values (A1, B1, C1, D1,E1 & F1) where G1 is the value of summation (Formula)
    Knowing that each of the data cells should have a ceiling of “9”
    So that:
    - If A1<=9 then the summation would return its original value, else if A1>9 the summation would return its value to “9”. For example, even if A1 value is “14”, then G1 should add its value as “9”
    - If B1<=9 then the summation would return its original value, else if B1>9 the summation would return its value to “9”

    The same loop should exist with C1, D1, E1 and F1

    The goal is not to change the cell value itself, but to add maximum “9” of its value to cell G1

    So that the summation result in G1 can be maximum equal to (9*6); 54

    I’m looking for a summation formula of data cells with different values (A1, B1, C1, D1,E1 & F1) where G1 is the value of summation
    Knowing that each of the data cells should have a ceiling of “9”
    - If A1<=9 then the summation should return its original value, else if A1>9 the summation would return its value to “9”. For example, even if A1 value is “14”, then G1 should add its value as “9”

    The same applies to other cells

    So that the summation result in G1 can be maximum equal to (9*6); 54

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Summation Formula of Several Values with a Ceiling

    Hi,

    Enter this array formula in G1:

    =SUM(IF(A1:F1>9,9,A1:F1))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Summation Formula of Several Values with a Ceiling

    Try
    =SUMPRODUCT(--(A1:F1<=9),A1:F1)+COUNTIF(A1:F1,">9")*9
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    06-08-2013
    Location
    Doha, Qatar
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Summation Formula of Several Values with a Ceiling

    Thank you Ace_XL. you saved me pal

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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