Pivot table sorting data

[gnanas]

I have a x-cel table like this

work order mmyyyy expenditure
101 200201 150.00
102 200201 165.00
103 200202 185.00

for every mmyyyy we have many work orders with different expenditures.

I need to find out the number of workorders in each month having following criteria
0<expenditure<1000
1000<expenditure<5000
5000<expenditure<10000 and so on

I am using IF function to insert values on a new column and use Pivot table to get the required information Can I get the results directly only by using pivot table and modifying the column field to suit my criteria

for eg in pivot table layout instead of using &quot;expenditure&quot; use &quot;expenditure<1000&quot;

Any help would be very much appreciated


Gnanas

 

[Zathras]

AFAIK your method of using an extra column that contains an expression for grouping is the only way to get your results in a pivot table.

There are a couple of things you may wish to investigate, however:

1. A small table in conjunction with the VLOOKUP function may be more appropriate than nested IF statements. (Especially if you have more than 7 groupings.)

2. You could get similar results using database techniques instead of a pivot table. Look in the help file for DSUM function and database techniques. Although from what I can gather from your post, in this case the pivot table is probably the better way to go.