Using Choose or some other method to choose between Formulas

[Vaslo]

Hello,

I have a spreadsheet that selects from one of four places depending on the variation of two criteria (Whether I want to look at Sales or Gallons and whether I want the Actual or the Forecast)

I have a formula I use below, but its quite long. I know CHOOSE() is a way to shorten functions. I don't know of a way to use CHOOSE() with Index/Match and can't seem to dig anything up. Is there a way to use CHOOSE() to change the table you are selecting, or is there a better way?

=IF(AND(N$2="Actual",$O$1="Sales"),SUMIFS(RawData[SumOfNet Invoiced Amount at AFX],RawData[CalendarMonth],N$3,RawData[Product Number],$S6,RawData[Profit Center Code],$F$4,RawData[Customer Name],$A$1),
IF(AND(N$2="Actual",$O$1="Gallons"),SUMIFS(RawData[SumOfNet Quantity in GAL],RawData[CalendarMonth],N$3,RawData[Product Number],$S6,RawData[Profit Center Code],$F$4,RawData[Customer Name],$A$1),
IF(AND(N$2="Forecast",$O$1="Sales"),SUMIFS(FoodFcst[Gross Sales],FoodFcst[FcstMonth],N$3,FoodFcst[Product '# as text],$S6,FoodFcst[Customer],$A$1),
IF(AND(N$2="Forecast",$O$1="Gallons"),SUMIFS(FoodFcst[Volume],FoodFcst[FcstMonth],N$3,FoodFcst[Product '# as text],$S6,FoodFcst[Customer],$A$1),
999))))

 

[ukmikeb]

Perhaps (untested) :-

=IF(N$2="Actual",SUMIFS(INDEX((RawData[SumOfNet Invoiced Amount at AFX],RawData[SumOfNet Quantity in GAL]),,,IF($O$1="Sales",1,IF($O$1="Gallons",2))),RawData[CalendarMonth],N$3,RawData[Product Number],$S6,RawData[Profit Center Code],$F$4,RawData[Customer Name],$A$1),
IF(N$2="Forecast",SUMIFS(INDEX((FoodFcst[Gross Sales],FoodFcst[Volume]),,,IF($O$1="Sales",1,IF($O$1="Gallons",2))),FoodFcst[FcstMonth],N$3,FoodFcst[Product '# as text],$S6,FoodFcst[Customer],$A$1),999))

hth