Sum of values using index match match?

[gobblechops]

Hi all,

I am trying to calculate the sum of a range where the row will meet the row will meet a 'category' criteria and the column will meet a 'range of months' criteria.

The row criteria will be one 'category' but may be contained in many or none rows.
The column criteria will be a range of months which can be changed dynamically.

So far I have =SUMIF(L5:L45,G6,INDEX(Q5:AB45,,MATCH(

I know this is someway along the right lines but the complication is creating the formula for the range of months.

L5:L45 is the rows of categories
G6 is the cell containing the category
Q5:AB45 is the range of values
Q4:AB4 contains the months
AA2 and AB2 contain the start month and end month to be changed as needed

Thank you in advance, looking forward to the result, any brief explanation of the formula to help with learning would be greatly appreciated.

 

[Fluff]

When posting a formula using < or > signs you need to put a space either side. Otherwise the board software sees that as HTML code.

 

[Aladin Akyurek]

What do you have in Q4:AB4 exactly? Dates?

 

[gobblechops]

Hi Aladin,

Q4 1/1/2018 (Jan-18) to AB4 1/12/2018 (Dec-18)

 

[Aladin Akyurek]

Hi Aladin,

Q4 1/1/2018 (Jan-18) to AB4 1/12/2018 (Dec-18)

"L5:L45 is the rows of categories
G6 is the cell containing the category
Q5:AB45 is the range of values
Q4:AB4 contains the months
AA2 and AB2 contain the start month and end month to be changed as needed"

Is this what we are after?

=SUM(IF($L$5:$L$45=G6,IF($Q$4:$AB$4>=AA2,IF($Q$4:$AB$4<=AB2,$Q$5:$AB$45))))

which must be confirmed with control+shift+enter, not just enter.

AA2 and AB2 are user entries in the form of say 2018-01-01 (Jan-18) and 2018-06-01 (Jun-18).

 

[gobblechops]

Works perfectly, thanks for your time.

 

[Aladin Akyurek]

Works perfectly, thanks for your time.

Glad to help.