=IFNA(SUM(VLOOKUP($A4,Consol!$C$8:$BS$222,MATCH(TB!G$1,Consol!$C$2:$BS$2,0),FALSE)),0) I have a project finance model (PFM) by month and I need the data to feed into a TB (also monthly). So what I need to do is sum multiple rows in the PFM that meet the criteria of being part of a specific GL code and I need the formula to pull the data when the date matches i.e in the TB for GL code 200 in JAN 19 I need it to find JAN 19 in the PFM and then sum all the rows that have been designated as GL code 200.
1 Answer
=IFERROR(SUMIF(Consol!$C$10:$C$147,TB!$A12,INDEX(Consol!$K$10:$CX$147,,MATCH(TB!J$1,Consol!$K$2:$CX$2,0))),0)
Where:
Consol!$C$10:$C$147 are the GL codes on the PFM model
TB!$A12 is the specific GL code on the TB
Consol!$K$10:$CX$147 is all of the relevant summing data in the PFM
TB!J$1 is the Date on the TB model
Consol!$K$2:$CX$2 are all the dates on the PFM
So the solution sums all matches that return the same GL code using SUMIF and then find the correct column to Sum using a combination of MATCH and INDEX
