• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

use index and match to sum column values with multiple matches

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

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

79476
 

Attachments

Last edited:
@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:
Top