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

Region	REG	DIST
ALL
CA-ALL	CA
CA-CAD	CA	CAD
CA-CCD	CA	CCD
WW	WW
NM-ALL	NM
NM-EWD	NM	EWD
NM-NMD	NM	NMD
IM	IM
PC	PC

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Reg	Dist
CA	CAD
CA	CAD
CA	CAD
CA	CAD
CA	CAD
CA	CAD
CA	CAD

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

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