Results 1 to 3 of 3

Thread: SUMIFS for a certain number of matches in a column

  1. #1

    SUMIFS for a certain number of matches in a column

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a worksheet with several columns of data. Column D contains a long list of the names of participants, of which there are only 11, but they could each appear over 100 times.

    I am looking for some help to write an equation to add up the numerical values from data from column H for each row when the players name is present in Column D and another criteria is met in Column J.

    It sounds like a simple SUMIFS, =SUMIFS($H$2:$H$1000,$D2:$D$1000,"Player Name",$J$2:$J$1000,"<>Wide")

    However, the bit I am struggling with is I want to add up the values in column H for the first 20 times the player appears in Column D and Column J does not contain the text "Wide".

    Then in another cell I would like to return the values for occurrences 21-40 for the player and so on in groups of 20 up to 121-140.

    I hope that this makes sense; any help gratefully received.
    Last edited by wozbits; 2014-08-25 at 10:00 PM. Reason: Formatting of post

  2. #2
    Good afternoon,

    This may not be the cleanest way, but you could add a helper column. Something like:

    = COUNTIF ( $D$22 , D2 , $J$2:J2 ,"<>Wide")

    in row 3 of an additional column and drag down. Then you could set up a table of values and reference it with your sumifs function. Have something like Name, Start, and End then add to your sumifs --- 'Helper', ">=" & 'Start' , 'Helper', "<=" & 'End' )

    Hope this helps. It might be simpler to explain with a sample workbook.

    Best of luck,

  3. #3
    Many thanks for your help, explanation and code for allowing me to solve my problem.
    It worked a treat - thank you; greatly appreciated.

Tags for this Thread

Posting Permissions

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