SUMIF column increment

[siphil99]

I have a SUMIF formula and I need to drag it down and want to increase the value for the column which I believe is referred to as an increment.

The below image shows the format, using the current Olympic Games medal table. Each total is calculated from a SUMIF calculation of winners for each country and as there more than 200 countries competing I want to drag down the SUMIF so that it refers to the next column automatically.

Can anyone suggest a solution?

Thanks.

 

[jasonb75]

You could use something based on

=SUMIF(Criteria range, criteria, INDEX(sum range all columns, 0, MATCH(country, sum range headers, 0)))

or you could use ROWS(B$2:B2) in place of the MATCH(...) part. With MATCH, the correct column would be chosen if the countries were in a different order, with ROWS it is assumed that the order is the same, if it is not then the results would be incorrect.