If Function, Indirect & Vlookup to return based on two requirements

Hello.

I have attached an extract of the data and what I am trying to achieve.

My current formula is:

=(IF($B$2="Full-Year",SUMIF(INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP(A4,Lookups!AK:AL,2,0)),A4,INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($A$2,Lookups!A:B,2,0))),SUMIFS(INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($A$2,Lookups!A:B,2,0)),INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP(A4,Lookups!AK:AL,2,0)),A4,INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($B$2,Lookups!AF:AH,3,0)),VLOOKUP($B$2,Lookups!AF:AG,2,0))))/1000

However it does not seem to be returning anything.

Thank you

Elouise

in your lookups for the range to sum, you are referencing column G which doesn't contain numbers!

Hopefully that's a start

Dav

Of course!

I think this fixes the problem - thank you very much

I would need it to follow what is in the drop down menu.

I.e. I need it to pick up Jan, Feb, Mar, Q1 etc. all individually

You need to get data from J not G for "Total all Verticals" (in your Lookups B2): I haven't looked further as=t the rest of the formula.

Also the division by 1000 is misplaced.

Hi,

This is great - thank you.

What I need now is to that it can be filtered on the vertical as well (i.e. A2)

Not sure how to incorporate that into the formula?

This was directed to you, sorry!

No worries, I'm looking into how to combine these two formulas maybe?

=(IF($S$2="Full-Year",SUMIF(INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP(R6,Lookups!AL:AM,2,0)),R6,INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($R$2,Lookups!A:B,2,0))),SUMIFS(INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($R$2,Lookups!A:B,2,0)),INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP(R6,Lookups!AL:AM,2,0)),R6,INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($S$2,Lookups!AG:AI,3,0)),VLOOKUP($S$2,Lookups!AG:AH,2,0)))

AND(IF($R$2="Total All Verticals",SUMIF(INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP(R6,Lookups!AL:AM,2,0)),R6,INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($R$2,Lookups!A:B,2,0))),SUMIFS(INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($R$2,Lookups!A:B,2,0)),INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP(R6,Lookups!AL:AM,2,0)),R6,INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($S$2,Lookups!AG:AI,3,0)),VLOOKUP($S$2,Lookups!AG:AH,2,0))))/1000)

Have you had any ideas?

Thanks

Elouise

As far as I can judge, the only difference is the division by 1000 on the second formula ?

if so try ....


=SUMIF(INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP(R6,Lookups!AL:AM,2,0)),R6,INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($R$2,Lookups!A:B,2,0))),SUMIFS(INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($R$2,Lookups!A:B,2,0)),INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP(R6,Lookups!AL:AM,2,0)),R6,INDIRECT("'"&"Current Forecast"&"'!"&VLOOKUP($S$2,Lookups!AG:AI,3,0)),VLOOKUP($S$2,Lookups!AG:AH,2,0)))/(IF($R$2="Total all Verticals",1000,1)