# use index and match to sum column values with multiple matches

#### monoj chakraborty

##### Member
Hey good people...

I am trying to sum a row of values, where the column header matches a given string....there will be multiple matches, precisely, one match every month for the 12 months chosen.

While I have got the result with Sumif and Sumproduct as will be shown in the file, I know there is a way to use the index and match function as well to get around to this. I am just not able to get it under my fingers.

In my case the index function is just returning the first instance of the match!

Any help appreciated

best regds.,

#### Attachments

• 84.5 KB Views: 8

#### herofox

##### Active Member
hi ..what the problem with Sumif it's work well ?

#### monoj chakraborty

##### Member
Hey, Sumif of course solves the problem for now and so does sumproduct.

I am looking to solve it thru' Index with some future needs in my mind where sumif could become cumbersome to use.

#### bosco_yip

##### Excel Ninja
As mentioned by herofox you could use Sumif or Sumproduct function.

But, by the way, you may use Sum+Index+Aggregate (or, Small If) function, and which is a longer formula.

In BY4, array ("Ctrl+Shift+Enter") formula copied down (for Excel 2010 and up):

=SUM(INDEX(AL4:BU4,N(IF(1,AGGREGATE(15,6,COLUMN(\$AL\$3:\$BU\$3)-COLUMN(\$AK\$3)/(\$BY\$3=\$AL\$3:\$BU\$3),ROW(INDIRECT("1:"&COUNT(\$AL\$2:\$BU\$2))))))))

Or, (for Excel 2007 and down)

=SUM(INDEX(AL4:BU4,N(IF(1,SMALL(IF((\$BY\$3=\$AL\$3:\$BU\$3),COLUMN(\$AL\$3:\$BU\$3)-COLUMN(\$AK\$3)),ROW(INDIRECT("1:"&COUNT(\$AL\$2:\$BU\$2))))))))

#### Attachments

• 40.1 KB Views: 2
Last edited:

#### monoj chakraborty

##### Member
Will definitely try this out...appreciate the help

#### monoj chakraborty

##### Member
@bosco_yip , I must say this is a very ingenious solution. Had a couple of question as I am working thru' the formula :

1. What does the "1" do after the If function?
2. Can the double dash (--) be used in place of "N"
3. Can the [k] parameter in the AGGREGATE function also be an array by itself? as the ROW function used as the last parameter also returns an array {1..12} to evaluate the smallest 12 numbers from the series returned by the AGGREGATE function
4. Is this acting as the ROW index or the COLUMN index for the INDEX function - I though we need to return the column numbers as an array; here it seems the array evaluated from the IF function is used to return the ROW number for the INDEX function; this is what is confusing me the most

Any help/clarification appreciated....I want to master the logic of this formula so that I can re-use this in other contexts.

Thanks

#### bosco_yip

##### Excel Ninja
@bosco_yip , I must say this is a very ingenious solution. Had a couple of question as I am working thru' the formula :

1. What does the "1" do after the If function?
2. Can the double dash (--) be used in place of "N"
3. Can the [k] parameter in the AGGREGATE function also be an array by itself? as the ROW function used as the last parameter also returns an array {1..12} to evaluate the smallest 12 numbers from the series returned by the AGGREGATE function
4. Is this acting as the ROW index or the COLUMN index for the INDEX function - I though we need to return the column numbers as an array; here it seems the array evaluated from the IF function is used to return the ROW number for the INDEX function; this is what is confusing me the most

Any help/clarification appreciated....I want to master the logic of this formula so that I can re-use this in other contexts.

Thanks
Please refer to this post of which I explain, " Why use N(IF(1,...) in the Index formula "

Summing Best 6 - Mixture of Grades & Numbers - Substitution On The Fly [SOLVED] (excelforum.com)

Regards

Last edited: