Match criteria in Column A and sum all rows below in Column D
Results 1 to 9 of 9

Thread: Match criteria in Column A and sum all rows below in Column D

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Match criteria in Column A and sum all rows below in Column D

    Hello,

    I am trying to find week number in C1 and match it in column A. Then sum all cells below to the end in column D. In this example it is sum of D8:D11.
    Is this possible to do?. The week number is going to be changing and would want to know the remaining amount for remaining weeks basically.
    It is basically a vlookup but how to do it to include a sum of all cells below also?,

    Please help



    A B C D
    1 Current Week 29
    2 Week Data 1 Data 2 Data 3
    3 24 1 1 1
    4 25 2 2 2
    5 26 3 3 3
    6 27 4 4 4
    7 28 5 5 5
    8 29 6 6 6
    9 30 7 7 7
    10 31 8 8 8
    11 32 9 9 9
    Last edited by Addictions; Jul 20th, 2019 at 10:57 AM.

  2. #2
    Board Regular navic's Avatar
    Join Date
    Jun 2015
    Location
    Europa - Croatia
    Posts
    201
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match criteria in Column A and sum all rows below in Column D

    If
    F2 = week number as criteria
    and
    G2 = Column header as criteria
    try
    Code:
    =SUMIF($A$3:$A$11,">="&F2,INDEX($B$3:$D$11,0,MATCH(G2,$B$2:$D$2,0)))
    in your case
    Code:
    =SUMIF($A$3:$A$11,">="&29,INDEX($B$3:$D$11,0,MATCH("Data 3",$B$2:$D$2,0)))
    Last edited by navic; Jul 20th, 2019 at 11:24 AM.

  3. #3
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,860
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match criteria in Column A and sum all rows below in Column D

    VBA solution:

    Code:
    Option Explicit
    
    
    Sub WeekSum()
        Dim lr As Long, crit As Integer, i As Long, x As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        crit = Range("C1").Value2
        For i = 3 To lr
            If Range("A" & i) = crit Then
                x = Application.WorksheetFunction.Sum(Range(Cells(i, 4), Cells(lr, 4)))
            End If
        Next i
        MsgBox ("Results of Sum equals " & x)
    End Sub
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  4. #4
    New Member
    Join Date
    May 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match criteria in Column A and sum all rows below in Column D

    Wow, Thank you so much for helping out on this one.
    I need to break it down now and analyse how this exactly works.
    Thank you again for your help!

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match criteria in Column A and sum all rows below in Column D

    I also really much appreciate VBA solution for this.
    Thank you awesome people to help me out!.

  6. #6
    New Member
    Join Date
    May 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match criteria in Column A and sum all rows below in Column D

    Quote Originally Posted by navic View Post
    If
    F2 = week number as criteria
    and
    G2 = Column header as criteria
    try
    Code:
    =SUMIF($A$3:$A$11,">="&F2,INDEX($B$3:$D$11,0,MATCH(G2,$B$2:$D$2,0)))
    in your case
    Code:
    =SUMIF($A$3:$A$11,">="&29,INDEX($B$3:$D$11,0,MATCH("Data 3",$B$2:$D$2,0)))
    Please could you possibly explain what ">="&29 really does in this formula?
    Especially this bit ">="&. Why greater and equal is in quotes and it follows with and symbol afterwards.

    Also how could I implement here COOUNTA to know the number of weeks calculated instead of having a total of numbers.
    Last edited by Addictions; Jul 20th, 2019 at 11:39 AM.

  7. #7
    Board Regular navic's Avatar
    Join Date
    Jun 2015
    Location
    Europa - Croatia
    Posts
    201
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match criteria in Column A and sum all rows below in Column D

    Quote Originally Posted by Addictions View Post
    Please could you possibly explain.
    I'll try to explain the formula, but keep in mind that English is not my mother tongue.

    To be clearer you need to know the arguments of the SUMIF function.
    Code:
    =SUMIF(range,criteria,sum_range)
    Functions arguments are separated by delimiter (,) or (; ).

    "range" argument, in this case is '$A$3:$A$11'. It contains the first condition ie. Row header (week number 29).

    "criteria" argument in this case week number 29 as condition. Since you want to add up the value of belonging to a number of week, the number 29 is set.
    If you want to add up all the below number 29, therefore we want to add multiple conditions or numbers, ie. all the numbers more than 29.

    This criterion consists of three parts.
    The first part is text string ">="
    Second part concatenate. For this purpose, you can use the '&' character (ampersand) that concatenate two or more values from multiple cells to produce one continuous text value.
    The third part is the value from cell 'F2'.

    So the results for these two terms are the same.
    Code:
    ">="&F2
    or
    Code:
    CONCATENATE(">=",F2)
    If you use the 'F9' key when evaluating the formula, then you will see that the result is
    Code:
    ">=29"
    "sum_range" argument
    When a formula finds a condition, using the INDEX function as a result it returns a value from the same row but from a specified column that has a specified header. In this case, it is 'Data 3' located in the 'G2' cell as a condition.
    Code:
    INDEX($B$3:$D$11,0,MATCH(G2,$B$2:$D$2,0)
    The result of the INDEX function is a value of 10, from the column with the header 'Data 3'.
    Nested MATCH function returns the number of column for the condition (in this case 3).
    So the SUMIF formula will return the SUM of all cells from the third column, which in column 'A' have number 29 and higher.
    So, the SUM of values in the rows of 8 to 11.

    I hope I have explained to you. If I'm not, then somebody on the forum will do it for me instead.
    Last edited by navic; Jul 20th, 2019 at 12:43 PM.

  8. #8
    New Member
    Join Date
    May 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match criteria in Column A and sum all rows below in Column D

    It does explain. Thank you for help

  9. #9
    Board Regular navic's Avatar
    Join Date
    Jun 2015
    Location
    Europa - Croatia
    Posts
    201
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match criteria in Column A and sum all rows below in Column D

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

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
  •