SUMIFS with MULTIPLE INDEX RANGES ROLL UP AND DRILL DOWN

[Grimm127]

Hi ALL,

I am currently using this formula
=SUMIFS(INDEX('2018A'!U:AF,0,$BG$8),INDEX('2018A'!A:H,0,$BG$10),$BF$10,INDEX('2018A'!A:H,0,$BG$11),$BF$11,INDEX('2018A'!A:H,0,$BG$12),$BF$12,'2018A'!J:J,$C$7)/1000
AKA - SUMIFS((MONTH COLUMN, MONTH ID),(REGION COULMN,REGION ID),(SUB-REGION COULMN,SUB-REGION ID),(TERMINAL COULMN, TERMINAL ID), (ACCOUNT COULMN, REVENUE ID)

THE ID'S ARE SELECTED VIA DROP DOWN COMBO BOXES. SO WHEN ALL MY DROP DOWN FIELDS ARE POPULATED THEN I GET THE DESIRED RESULT.
HOWEVER, HERE IS THE RUB. WHEN I ONLY WANT TO SUM JUST THE TWO OF THE THREE (REGION, SUB-REGION). MAKING THE THIRD DROP DOWN FIELD EQUAL TO "ALL" I GET A #VALUE ! ERROR. MONTH AND ACCOUNT ARE CONSTANTS AND ALWAYS HAVE A VALUE.

QUESTION, HOW CAN I GET THIS FORMULA TO SUMIFS AND SOMEHOW IGNORE THE THIRD PORTION AND ULTAIMATLEY THE 2ND PORTION AS WELL OF THE FORMULA SO I CAN GET THE DESIRED RETURN? THE IDEA IS A ROLL UP TOTAL AND DRILLING DOWN TO A TERMINAL LEVEL

THANK YOU IN ADVANCE

 

[SpillerBD]

You may need to implement two different formulas based on the "ALL" possibility, where If ALL use SUMIFS without that condition.

I believe a Pivot Table summary using SLICERS in place of your Combo boxes should be considered a possible solution.

 

[Grimm127]

Thank you for your reply. I am not at all familiar with slicers. is there a link I can go to where I can familiarize myself about slicers?

Best regards