Search code examples
excelindexingmatchsumifs

Excel Index and Match Sumifs with multiple rows and variable number of columns

I am working on finding the sum of values in a data table with a variable number of rows for each value and a defined number of columns.

The formula below works to sum the rows in the data table based upon a single column but I cannot see how to sum multiple columns and rows.

=-SUMIFS(INDEX(NOMINAL_LEDGER,,MATCH("BALANCE_MTH"&$G$3,NOMINAL_LEDGER[#Headers],0)),
               NOMINAL_LEDGER[[Category]:[Category]],$I11)

G3 references the particular month and I11 references a category.

I can use Index and Match to find the total for a single row from BALANCE_MTHx to BALANCE_MTHy.

I've tried SUMPRODUCT and SUM(SUMIFS(INDEX.. to define the start and end columns but an error is always returned.

I don't know if I'm missing something basic or this is an issue with trying to use a data table.

Sample Data added below:

Category     BALANCE_MTH1    BALANCE_MTH2    BALANCE_MTH3  
Training             2500            3250            4100  
Travel               1100             875            1800  
Training             1500             750             900  
Marketing            7500            4300            6000    
Training             2250            1250            2100
Solution

  • Use SUMPRODUCT with INDIRECT to get the columns:

    =SUMPRODUCT((NOMINAL_LEDGER[[Category]:[Category]] = $G$3)*INDIRECT("NOMINAL_LEDGER[[BALANCE_MTH" & G1 & "]:[BALANCE_MTH" & G2 & "]]"))

    enter image description here