sumifs dynamic critiria

[rossking]

So i have a drop down (B2) that is fed from COL 1 of the Help table

Depending on Which is selected my SumIfs either has no criteria (All the whole dataset), Region (Use Col 1 of the data Table and only add matches) Region-District (Use COL 1 & COL 2)

Col M = Sales

A dynamic Criteria based on the Vlookup return

Is there a simpler way than this

=IF(B2="ALL",SUMIFS('Customer Data'!M:M),IF(VLOOKUP(B2,Help!A:C,3,FALSE)="",SUMIFS('Customer Data'!M:M,'Customer Data'!B:B,VLOOKUP(B2,Help!A:C,2,FALSE)),SUMIFS('Customer Data'!M:M,'Customer Data'!B:B,VLOOKUP(B2,Help!A:C,2,FALSE),'Customer Data'!C:C,VLOOKUP(B2,Help!A:C,3,FALSE))))


HELP Table

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]REG[/TD]
[TD="width: 64"]DIST[/TD]
[/TR]
[TR]
[TD]ALL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CA-ALL[/TD]
[TD]CA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CA-CAD[/TD]
[TD]CA[/TD]
[TD]CAD[/TD]
[/TR]
[TR]
[TD]CA-CCD[/TD]
[TD]CA[/TD]
[TD]CCD[/TD]
[/TR]
[TR]
[TD]WW[/TD]
[TD]WW[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NM-ALL[/TD]
[TD]NM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NM-EWD[/TD]
[TD]NM[/TD]
[TD]EWD[/TD]
[/TR]
[TR]
[TD]NM-NMD[/TD]
[TD]NM[/TD]
[TD]NMD[/TD]
[/TR]
[TR]
[TD]IM[/TD]
[TD]IM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PC[/TD]
[TD]PC[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 93"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Reg[/TD]
[TD]Dist[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]CAD[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]CAD[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]CAD[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]CAD[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]CAD[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]CAD[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]CAD[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What are the values B2 can show besides ALL? Region and Region-District do not show in your HELP Table...