Select multiple rows & columns based on date range

[prabhu91]

Hello,
I have a set of data where each column (except for the first) represent month and each row represent a unique data item. I need to select all rows and few columns that represent the months in a given date range (see cell B16 and B17). The end goal is to create an array of sums (see formula in cell B19) and plot that array in a graph...

I can use INDEX and MATCH function to select the required columns for 1 row (see formula in cell B20), but the same extended to multiple rows is failing (see formula in cell B21).

What am I missing?

-prabhu

 

[chirayu]

see attached

 

[prabhu91]

see attached

Hi Chirayu,
This is very good! Thank you so much.

However, I need to created an array of sums (i.e. sum each row in to one element in the array). The SUM function doesn't work with array formulas...

Example: the result (cell F22) in the attachment should be {0;8;0;4;0;15;6;6;3;2;3;5;1} (for Start Date = Jan-19 and End date = Mar-19)

Help?

-prabhu

 

[bosco_yip]

Hi prabhu,

Here's the fix of your formulae :

1] Sum Array (for Start Date = Jan-19 and End date = Mar-19)
Define Name >> Name : Sum_Range
Refer to : =INDEX(Sheet1!$B$2:$Y$2,MATCH(StartDate,Sheet1!$B$1:$Y$1,0)):INDEX(Sheet1!$B$14:$Y$14,MATCH(EndDate,Sheet1!$B$1:$Y$1,0))
Then, in B19, formula :
=MMULT(Sum_Range,TRANSPOSE(COLUMN(Sum_Range)^0))
the formula become >>
=MMULT({0,0,0;3,3,2;0,0,0;0,4,0;0,0,0;6,1,8;0,0,6;0,3,3;0,0,3;0,0,2;0,2,1;1,0,4;0,1,0},{0;0;0})
and finally return >>
={0;8;0;4;0;15;6;6;3;2;3;5;1}

2] Selected area (<Single Row>)
B20, formula :
=INDEX($B$2:$Y$2,MATCH(StartDate,$B$1:$Y$1,0)):INDEX($B$2:$Y$2,MATCH(EndDate,$B$1:$Y$1,0))
the formula return >>
={0,0,0}

3] Selected area (<Multi Row>)
B21, formula :
=INDEX($B$2:$Y$2,MATCH(StartDate,$B$1:$Y$1,0)):INDEX($B$14:$Y$14,MATCH(EndDate,$B$1:$Y$1,0))
the formula return >>
={0,0,0;3,3,2;0,0,0;0,4,0;0,0,0;6,1,8;0,0,6;0,3,3;0,0,3;0,0,2;0,2,1;1,0,4;0,1,0}

4] see attached file

Regards
Bosco

 

[Juniad]

You could use this formula as well.
=SUM(INDIRECT(ADDRESS(2,MATCH($B$16,1:1,0))&":"&ADDRESS(COUNTA($A$1:$A$14)+1,MATCH($B$17,1:1,0))))

Hit like if it works.

 

[prabhu91]

Here's the fix of your formulae :

1] Sum Array
Define Name >> Name : Sum_Range
Refer to : =INDEX(Sheet1!$B$2:$Y$2,MATCH(StartDate,Sheet1!$B$1:$Y$1,0)):INDEX(Sheet1!$B$14:$Y$14,MATCH(EndDate,Sheet1!$B$1:$Y$1,0))
B19, formula :
=MMULT(Sum_Range,TRANSPOSE(COLUMN(Sum_Range)^0))
the formula become >>
=MMULT({0,0,0;3,3,2;0,0,0;0,4,0;0,0,0;6,1,8;0,0,6;0,3,3;0,0,3;0,0,2;0,2,1;1,0,4;0,1,0},{0;0;0})
and finally return >>
={0;8;0;4;0;15;6;6;3;2;3;5;1}

2] Selected area (<Single Row>)
B20, formula :
=INDEX($B$2:$Y$2,MATCH(StartDate,$B$1:$Y$1,0)):INDEX($B$2:$Y$2,MATCH(EndDate,$B$1:$Y$1,0))
the formula return >>
={0,0,0}

3] Selected area (<Multi Row>)
B21, formula :
=INDEX($B$2:$Y$2,MATCH(StartDate,$B$1:$Y$1,0)):INDEX($B$14:$Y$14,MATCH(EndDate,$B$1:$Y$1,0))
the formula return >>
={0,0,0;3,3,2;0,0,0;0,4,0;0,0,0;6,1,8;0,0,6;0,3,3;0,0,3;0,0,2;0,2,1;1,0,4;0,1,0}

4] see attached file

Regards
Bosco

Thanks Bosco, I like your solution (and it helped me understand the INDEX function little bit better )

Problem solved now!

-prabhu