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

1. ## SUMIFS for a certain number of matches in a column

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.

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. bgoree09
Many thanks for your help, explanation and code for allowing me to solve my problem.
It worked a treat - thank you; greatly appreciated.