Summing up values with a special condition
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Summing up values with a special condition

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello,

    I've got a row with YES or NO in every cells and I would like to sum up all the values under the NOs with the first Yes, until it reaches the next YES. How can it be done?
    Ex.
    YES NO NO NO ¦YES NO NO ¦YES...
    Values 5 5 10 10 ¦15 10 15 ¦5
    >Result 30 ¦40 ¦...

    Hope you understand. Thank you in advance for you help.
    Fred.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,868
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-07 08:04, FCH wrote:
    Hello,

    I've got a row with YES or NO in every cells and I would like to sum up all the values under the NOs with the first Yes, until it reaches the next YES. How can it be done?
    Ex.
    YES NO NO NO ¦YES NO NO ¦YES...
    Values 5 5 10 10 ¦15 10 15 ¦5
    >Result 30 ¦40 ¦...

    Hope you understand. Thank you in advance for you help.
    Fred.
    Lets say that A1:H1 houses the YES/NO data and A2:H2 the corresponding values:

    In A3 enter and copy across till H3:

    =IF(A1="YES",SUM(A$2:INDIRECT(ADDRESS(2,MATCH(A1,B1:$H$1,0)+COLUMN()-1))),"")

    Aladin

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-07 21:54, Aladin Akyurek wrote:
    On 2002-03-07 08:04, FCH wrote:
    Hello,

    I've got a row with YES or NO in every cells and I would like to sum up all the values under the NOs with the first Yes, until it reaches the next YES. How can it be done?
    Ex.
    YES NO NO NO ¦YES NO NO ¦YES...
    Values 5 5 10 10 ¦15 10 15 ¦5
    >Result 30 ¦40 ¦...

    Hope you understand. Thank you in advance for you help.
    Fred.
    Lets say that A1:H1 houses the YES/NO data and A2:H2 the corresponding values:

    In A3 enter and copy across till H3:

    =IF(A1="YES",SUM(A$2:INDIRECT(ADDRESS(2,MATCH(A1,B1:$H$1,0)+COLUMN()-1))),"")

    Aladin
    Great, it works. Thanks a lot.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com