SUMIFS for a certain number of matches in a column

wozbits

New member
Joined
Aug 25, 2014
Messages
2
Reaction score
0
Points
0
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:

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

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

= COUNTIF ( $D$2:D2 , 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,
 

wozbits

New member
Joined
Aug 25, 2014
Messages
2
Reaction score
0
Points
0
bgoree09
Many thanks for your help, explanation and code for allowing me to solve my problem.
It worked a treat - thank you; greatly appreciated.
 
Top