Conditional Column and Row information pull formula help

[Michael Howarth]

Hello,

I need help pulling data in a formula where I have a table of information, cost type along the columns (R&M, Salaries etc) and GL String code along the rows.

I can pull the data using a sumif formula to summarise the cost type manually but I am struggling to filter the information by cost type via formula. therefore I need to change the rows on each formula to move along the rows and get the filtered subtotal amount.

I have attached the example sheet for reference. What I need is a formula which will auto populate with the row type filtered and the column request filtered.

Can anyone help please.

Thanks in advance

Michael

[#2 · Accepted solution]

Hi Michael,

The formula could be like

=SUMPRODUCT(($D$2:$D$11=$B17)*INDEX(OFFSET($E$2:$E$11,0,MATCH($C17,$E$1:$K$1,0)-1),0))

First multiplier checks the code, second one selects the column based on MATCH shifting it from first one (E) by OFFSET

Please see attached

[#3]

Thanks very much