0

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.

Ram
  • 3,092
  • 10
  • 40
  • 56
user3164272
  • 565
  • 1
  • 9
  • 20

2 Answers2

2

Try this formula

=SUMPRODUCT(--(A2:A200>=Y2),INDEX(B2:E200,0,MATCH(Y2,B1:E1,0)))

It is using MATCH to find which column to look at with the range B2:E200 (the 0, means all rows in that selected column). The test A2:A200>=Y2 checks the first column as you specified, the -- changes matches to 1 misses to 0, and SUMPRODUCT then multiplies those 1/09 by the selected column values to get the result.

If you want further reading see here

Bob Phillips
  • 437
  • 1
  • 3
  • 7
2

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.

  • That is exactly what I was looking for! I was playing around with the INDEX function, but couldn't quite get it right the way you did. Thanks a ton! It works like a charm :) – user3164272 Nov 22 '15 at 19:23