Summing large amounts of data based on certain attributes

Hello,

I have several thousand rows of data. Each row has an ID code (three letters), a date, a two numbers, which we'll call X and Y. My goal is to print out the sum of all Y for each X that falls between certain dates (for each ID code). I was going to try to do this using regular excel formulas but it quickly became too complicated and I think VBA is probably the way to go.

Here is an example of what I'm trying to accomplish. Say I have the following table:

ID Code	# Y	# X	Date
ABC	10	5	18-Jun
ABC	15	10	19-Jun
ABC	14	12	19-Jun
ABC	13	15	20-Jun
ABC	20	25	10-Jun
ABC	14	23	11-Jun
ABC	18	21	12-Jun
ABC	23	24	13-Jun
ABC	22	16	15-Jun
DEF	14	8	20-Jun
DEF	15	13	21-Jun
DEF	16	12	20-Jun
DEF	19	11	12-Jun
DEF	21	13	13-Jun
DEF	20	12	15-Jun
DEF	21	15	16-Jun

For each ID code (in this case ABC and DEF), I'm looking to take the weekly sum of #X for different ranges of Y. Assume I already have populated cells with the dates of each week end and that ranges are static. I'd want to print out the following tables:

For ID code ABC:

Range of Y values	Week ended 15-Jun	Week ended 22-Jun
10-12	0	5
12-15	23	27 (15+12)
15-18	0	10
18-21	46 (25+21)	0
21-24	40 (24+16)	0

In other words, the X values 24 and 16 would be added to get the 40 from the week ended 15-Jun for the range 21-24, since the corresponding Y values of 24 and 16 are 23 and 22 (and thus fall in the 21-24 range). Similarly, the X values 12 and 15 would be added to get the 27 from the week ended 22-Jun since their Y-values are 14 and 13 (which fall between 12-15).

The DEF table produced would look like this:

Range of Y values	Week ended 15-Jun	Week ended 22-Jun
10-12	0	0
12-15	0	8
15-18	0	25 (13+12)
18-21	23 (11+12)	0
21-24	25 (13+15)	0

The X-values 13 and 15 would be added together to get 25, since their corresponding Y-values are both 21. X-values 13 and 12 (with corresponding Y-values 15 and 16) are added to get 25, etc etc.

If you've read this far, THANK YOU. I know this is a headache, which is why I'm turning to the gurus. Any idea how I'd approach a VBA script to accomplish this summation?

Thanks in advance

If you Range of Y values were not overlapping, ie. instead of 10-12 and 12-15, having 10-12 and 13-15, then a Pivot Table would have worked nicely....

Select ABC or DEF from the dropdown

If you convert the data to a List (through Data|List)... then everytime you add anything to the table, and then refresh the Pivot... you'll be updated.

Thanks. Additionally, is there a way to incorporate a formula to decide whether to include each data point in the summation? For example, could I specify that I only want to include X values within a certain range of a given number in the total sums?

If I understand correctly, I think you would need to add another column to the database with a formula to check that...

e.g. in E2: =IF(AND(B2>=10,B2<=20),C2,0) copied down.

Then adjust Pivot table to sum that column Instead....

I don't know what you mean... sample please?

Ok, no. You can't use "variable" ranges like that, because the range is set through right-click >> group and show detail >> group and the entries are hard coded and can't refer to ranges.

Maybe if someone can do it with a VBA approach, I don't know....