1
=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.

Image 1 is the TB

Image 2 is the PFM

  • Hello, by the sounds of it, it is very much possible. But it's quite criptic without sample data. So please throw together some markdown sample data that would represent your data layout and include the expected output. Please also include your own attempt, you might be close to an answer. – JvdV Aug 20 '19 at 15:39
  • this is the formula that I am using =IFNA(SUM(VLOOKUP($A4,Consol!$C$8:$BS$222,MATCH(TB!G$1,Consol!$C$2:$BS$2,0),FALSE)),0) but it is only summing the first match that the vlookup finds – Matt Owen Aug 20 '19 at 15:40
  • The consol tab is the PFM – Matt Owen Aug 20 '19 at 15:41
  • I believe you want SUMIFS not VLOOKUP. – Scott Craner Aug 20 '19 at 15:49
  • 1
    Great you could solve it! Please post the solution as an answer instead of a comment. – Máté Juhász Aug 21 '19 at 3:23
0

=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:

  1. Consol!$C$10:$C$147 are the GL codes on the PFM model

  2. TB!$A12 is the specific GL code on the TB

  3. Consol!$K$10:$CX$147 is all of the relevant summing data in the PFM

  4. TB!J$1 is the Date on the TB model

  5. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.