Summing on Cust_ID, and Date Range Criteria

[hycho]

Hi,

I have two tables: 1) Tbl1 is a user input table and 2) Tbl2 is a pivot table of sales data by Cust_ID and YearMonth.

Below is just a sample data, but you can imagine the pivot table can get quite large due to the number customers and YearMonths. Instead of manually updating the pivot table, is there a way I can my conditions to get the sales count by a date range and cust_id.

For example, for Cust_ID, for the YearMonth period 201301 - 201303, the total sales amount would be 3 (see tbl1 for other examples). I was thinking of using sumproduct, but then the condition spans a time range, and I couldn't just use 3 conditions.

Is there a formula or a combination of functions I can used to compute the total sales base on cust_id and date range? Or any other more efficient solutions would be appreciated.

Thanks,

Hycho

Tbl1: Input Date Range
Cust_ID	Bgn_YM	End_Ym	Sales
1001	201301	201303	3
1002	201306	201312	63
1006	201312	201401	50

<tbody>
</tbody>

Tbl2: Details of Sales by YearMonth
Cust_ID	201301	201302	201303	201304	201305	201306	201307	201308	201309	201310	201311	201312	201401
1001	1	1	1	1	1	1	1	1	1	1	1	1	1
1002	1	2	3	4	5	6	7	8	9	10	11	12	13
1006	2	4	6	8	10	12	14	16	18	20	22	24	26

<tbody>
</tbody>