use index and match to sum column values with multiple matches

[monoj chakraborty]

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.,

 

[herofox]

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

 

[monoj chakraborty]

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]

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))))))))

 

[monoj chakraborty]

Will definitely try this out...appreciate the help

 

[monoj chakraborty]

@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]

@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

 

[monoj chakraborty]

excellent blog @bosco_yip