Simple(er) formula for summing multiple vlookups

[adllewis42]

Just FYI, I have a formula that works but I feel like there has to be a simpler way to make it happen. I figured I would ask and see what you all have to say.

I am trying to automate a process in financial reporting and, as part of that, I am constructing a worksheet that pulls together a lot of data. One thing I am trying to get is rolling 3mo data in aggregate. The reports from our accounting system always put the last three months in columns 6, 8, and 10.

Right now, Im using a formula that looks like this...

=round((vlookup("Adjusted EBITDA",$A:$J,10,false)+vlookup("Adjusted EBITDA",$A:$J,8,false)+vlookup("Adjusted EBITDA",$A:$J,6,false))/1000000,1)

That gives me the sum of the 3 most recent months in one cell. I thought about using some kind of sumif but since the names of the months (and years) will always be changing, I didnt think it would work.

If anyone is willing to help, it would be greatly appreciated.

Thanks.

 

[Excelestial]

=round(sumifs($F:$F,"Adjusted EBITDA",$H:$H,"Adjusted EBITDA",$J:$J, "Adjusted EBITDA")/1000000,1)

 

[pgc01]

Hi

This is your formula simplified:

=ROUND(SUMPRODUCT(VLOOKUP("Adjusted EBITDA",$A:$J,{6,8,10},FALSE))/1000000,1)

HTH

 

[adllewis42]

Hi

This is your formula simplified:

=ROUND(SUMPRODUCT(VLOOKUP("Adjusted EBITDA",$A:$J,{6,8,10},FALSE))/1000000,1)

HTH

Youre amazing. This is exactly what I was looking for. I knew it could be done but I just didnt know how. Thank you so much!

 

[pgc01]

I'm glad it helped. Cheers!

 

[adllewis42]

=round(sumifs($F:$F,"Adjusted EBITDA",$H:$H,"Adjusted EBITDA",$J:$J, "Adjusted EBITDA")/1000000,1)

I dont think that would work, would it? The term 'Adjusted EBITDA' doesnt appear in the columns F, H, or J. Its listed as a line item in column A and then the other columns show the amount for whatever period you happen to be looking at.