CODEWITHSUNDEEP
excel
Hilly1
Hilly1

Reputation: 157

Trouble using Vlookup in a SUMIFS function statement

I have an accounting spreadsheet where I am trying to create an analysis area which populates from the 12 month Actuals data so that when I enter a month, the function (in this case a SUMIF) will update the formula based on the specific column which contains the monthly data.

For example:

Cell O2 = the user input Month, for example: APR

Here is what the data looks like:

Product     Department      ACTUALS-APR ACTUALS-MAY ACTUALS-JUN …..

I have a Lookup table called: “MonthActuals” which looks as follows:

Month       Column
JAN     F
FEB     G   
MAR     H
APR     I
Etc…..

I have hard coded the other criteria to look up the actual amount, which are the Product and Department. So, the SUMIFS looks at the hard-coded Product and Department, but needs to look up the column for the month that the user wants the figures.

Here is my formula that works correctly WITHOUT using a VLOOKUP ( I hard-coded the month):

=SUMIFS(Sheet2!$F$8:$F$328,Sheet2!$B$8:$B$328," Total Glass Purchases ",Sheet2!$C$8:$C$328,"Scrap")

Here is my VLOOKUP which works correctly: 

=VLOOKUP(E1,MonthActuals,2,FALSE)

HOWEVER, when I replace the “F” column reference in the formula with a VLOOKUP formula, I get a #value error message.

Here is the formula I used when I tried to combine the VLOOKUP with the SUMIFS.

'SUMIFS(VLOOKUP(O1,MONTHACTUALS,2,FALSE)&”8:”&VLOOKUP(O1,MONTHACTUALS,2,FALSE)&“328”,Sheet2!$B$8:$B$328,"Total Glass Purchases",Sheet2!$C$8:$C$328,"Scrap")

I don't want to use any VBA, but only use functions to do these calculations.

Upvotes: 0

Views: 132

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

First be careful of ” they are not recognized by Excel, make sure they are all "

Next you will need to use INDIRECT()

SUMIFS(INDIRECT("Sheet2!" & VLOOKUP(O1,MONTHACTUALS,2,FALSE) & "8:" & VLOOKUP(O1,MONTHACTUALS,2,FALSE) & "328"),Sheet2!$B$8:$B$328,"Total Glass Purchases",Sheet2!$C$8:$C$328,"Scrap")

Upvotes: 2

Related Questions