Calculating a range of data

I'm trying to figure out how I can automate a fairly lengthy process and hoping someone can give me some pointers or direction. I've got a range of data and I want to narrow it down to ranges, like $0-$500, $500-$100, etc. and count the total number of occurrences and the sum of those occurrences within the range. Right now I've got to filter between x and y then do a count, and sum of those fields. I know there's a way to automate this so it can just attack the entire column in one shot I'm just not familiar with vb in excel.

I was thinking of writing a small program in vb.net that would do this but that just seems silly to do everything but this one function. Not to mention I can't figure a way of doing it without having an arse load of variables. Any help, ideas, links would be appreciated, TIA.

You can quickly group like that using a pivot table (also possible with formulae, but generally not such a good option as the pivot table).

I've attached a simple pivot table example for reference.

Given that, perhaps a .Net function isn't necessary for this part of you project?

Dude that was too bloody simple, another case of over thinking a situation. I'm slowly learning about pivot tables. Thanks again.

OK next question is it possible to group them by differing amounts? An example would look like this:

0-500
500-1000
1000-2500
2500-5000
5000-10000
10000-25000
25000-50000
50000-100000

The first two are fine to group by 500 however the rest gradually get further apart. Is this something that can be done with pivot tables?

I have Excel 2003 on the PC in front of me and I do not believe that sort of functionality is built into Pivot Table grouping. The workaround would be to add a helper column to your source table to define the grouping which you would use in your row field.

Cool beans, I'll look into that since that's a new term to me. Thanks for the pointers and help.

Sure, no problem. Let me know if you're struggling with it and I'll attach an example for you to look at.

Cool thanks. I did a quick google search and couldn't find anything by that name "Helper field". If you wouldn't mind doing a quick example for me so that I can understand it a little better to apply towards my other project.

Edit, if I could read I would have noticed you said column not field. Looking that up generates a lot more hits. If still wouldn't mind posting an example I would appreciate it.

A "helper column" is just an extra field in your table which contains some extra information you need to perform a task or analysis. It's typically formula-driven and based on other information in the table. So, in this case, it will be a column of data that specifies the grouping that each record belongs to.

or you can use VBA for Excel to get data already grouped by the amount.
Ever wonder what happened if you push ALT+F11 keys while Excel is opened?
In VBA use loop to go from one group to second one by changing clause where in select statement.
As example:
n=1
do until n=6
select case n
case 1
select all from table where amount between 0 and 500
case 2
select all from table where amount between 501 and 1000
case 3
select all from table where amount between 1001 and 5000
case 4
select all from table where amount between 5001 and 10000
case 5
select all from table where amount between 10001 and 50000
end select
Execute select statement and write data to the worksheet. Do not forget to move cursor to a row next to the one you used last.
Or to make it easier for you -- dump data into different worksheets all together.
n=n+1
loop


Have a fun.

For the helper field, as Colin mentioned, a lookup table is the way to go:

0 0-499
500 500-999
1000 1000-2499
2500 2500-4999
5000 5000-9999
10000 10000-24999
25000 25000-4999
50000 50000+

Put this table somewhere in your workbook, then do a VLOOKUP on the item you want to look up. Make sure the last argument of the VLOOKUP function is TRUE.