Forum Discussion

Michael Howarth's avatar
Michael Howarth
Copper Contributor
Oct 26, 2017
Solved

Conditional Column and Row information pull formula help

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

 

 

BI & Data Analysis
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 26, 2017

    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

     

     

     

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 26, 2017

    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

     

     

     

Resources