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


[TABLE="width: 215"]
<tbody>[TR]
[TD="colspan: 3"]Tbl1: Input Date Range[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust_ID[/TD]
[TD]Bgn_YM[/TD]
[TD]End_Ym[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD="align: right"]1001[/TD]
[TD="align: right"]201301[/TD]
[TD="align: right"]201303[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD="align: right"]201306[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD="align: right"]1006[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]201401[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 705"]
<tbody>[TR]
[TD="colspan: 5"]Tbl2: Details of Sales by YearMonth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust_ID[/TD]
[TD="align: right"]201301[/TD]
[TD="align: right"]201302[/TD]
[TD="align: right"]201303[/TD]
[TD="align: right"]201304[/TD]
[TD="align: right"]201305[/TD]
[TD="align: right"]201306[/TD]
[TD="align: right"]201307[/TD]
[TD="align: right"]201308[/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]201401[/TD]
[/TR]
[TR]
[TD="align: right"]1001[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD] 1[/TD]
[TD] 2[/TD]
[TD] 3[/TD]
[TD] 4[/TD]
[TD] 5[/TD]
[TD] 6[/TD]
[TD] 7[/TD]
[TD] 8[/TD]
[TD] 9[/TD]
[TD] 10[/TD]
[TD] 11[/TD]
[TD] 12[/TD]
[TD] 13[/TD]
[/TR]
[TR]
[TD="align: right"]1006[/TD]
[TD] 2[/TD]
[TD] 4[/TD]
[TD] 6[/TD]
[TD] 8[/TD]
[TD] 10[/TD]
[TD] 12[/TD]
[TD] 14[/TD]
[TD] 16[/TD]
[TD] 18[/TD]
[TD] 20[/TD]
[TD] 22[/TD]
[TD] 24[/TD]
[TD] 26[/TD]
[/TR]
</tbody>[/TABLE]