Search code examples
excelexcel-formulasumifsexcel-match

SUMIFS: How to write column names as formula while specifying a range?

There are columns B through Z in the table. I want to sum the column that contains "Mar-15" as the heading, provided column A's value is greater than or equal to "Mar-15".

So, if I have the following in Sheet1 -

     A       B       C       D       E
1          Mar-15   Apr-15  May-15  Jun-15
2 Feb-15    1       2       3       4
3 Mar-15    100     200     300     400
4 Apr-15    1000    2000    3000    4000
5 May-15    10000   20000   30000   40000

And I have the following in columns Y and Z -

    Y       Z
1 Month    Value
2 Mar-15    ?

I want to be able to write a formula in Z2, that sums the values in the column which has the heading equal to Y2, and where column A is greater than or equal to Y2. So here, the required answer for the formula would be 1110 since Y2 contains Mar-15.

To be clearer, if Y2 contains Apr-15, then the result in Z2 should be 22000

Now, I have the formula

=MATCH(Y2,$1:$1,0)

which gives me 2, which is the column index in the first row, that matches with the value in Y2.

Then, I modify it to get the address of the heading -

=ADDRESS(1,MATCH(Y2,$1:$1,0))

This gives me $B$1. To get the column alone, I am doing =LEFT(ADDRESS(1,MATCH(Y2,$1:$1,0)),2) which gives me $B

Now, I want to do a SUMIFS of the column returned by this address function, and specify the condition that column A should be greater than the value in Y2.

=SUMIFS(<something>,A:A,">="&Y2)

What should something be? Depending on the value in Y2, the column which I want summed can be different. So what formula do I insert in place of something?

I tried LEFT(ADDRESS(1,MATCH(Y2,$1:$1,0)),2):LEFT(ADDRESS(1,MATCH(Y2,$1:$1,0)),2) since LEFT(ADDRESS(1,MATCH(Y2,$1:$1,0)),2) gives me $B and I presumed this formula would give me $B:$B, but that doesn't work.

Solution

  • A row or column can be 'spliced' out of a rectangular matrix with the INDEX function. A , 0, or empty parameter (e.g. , ,) is used to note all cells in the opposite parameter. In this case, a 0 will mean all rows in the column that is selected.

          sumif_index

    The formula in Z2 can be one either of these,

    =SUMIF(A:A, ">="&Y2, INDEX(B:E, 0, MATCH(Y2, B$1:E$1, 0)))
=SUMIFS(INDEX(B:E, 0, MATCH(Y2, B$1:E$1, 0)),A:A, ">="&Y2)

    If you have additional values in Y3, etc. fill down as necessary.