sumifs formula help

[kamjam]

Hi everyone , I just learnt about the sumifs formula's but I don't get it ! I mean I do the simple ones but I am having trouble with the below , could some help me please , In column "I19" i need formula to sum all  "payable" in Period 5 from TAB "Rent fc" , rather than adding each cell is there a sumifs formula I could use ? 

[#2 · Accepted solution]

@kamjam so the formula is quite so simple because you are trying to index both columns and rows.  I mean you could have a simple formula that is specific for that 1 column of data but here is a more general solution that I think will work for you but assumes you have Excel365.  If you don't we can get rid of the LET statement (used to explain the different steps) and may need to get rid of the XMATCH depending on your version of Excel.

=LET(input,'rent fc '!$A$1:$N$33,  
                             h_input, "input is the input range from rent fc tab",
      colnum,XMATCH("P"&MID('Summary fcast'!B2,8,99)&"*",INDEX(input,3,),2), 
                             h_colnum, "colnum uses the Period column in the summary  table to find which column in the rent fc should be used",
          thisp,INDEX(input,,colnum),
                             h_thisp, "thisp uses colnum to pull out only the column of interest from the original input range",
           final,SUMIF(INDEX(input,,2),"Payable",thisp),
                             h_final, "final then uses the SUMIF to only sum up rows in the column of interest (thisp) if the title in column 2 says 'Payable'",
           final)