Need formula to pull in # of something based on a range

[BankBob]

client ID Accounts
12c3 4.3
12c3 6.5
12c3 9.1
14d4 2.2
14d4 5.3

Okay, so I have a spreadsheet with about 30,000 rows of data. I need to first run a pivot table that will give me the client ID #s and a count a accounts. Then I want to do a vlookup on the client ID # and create several columns that show how many accounts are within specific ranges, i.e. 0 - 5, 5.1 - 10, 10.1 +.

The above example is just a few rows of the 30,000+ rows and 10 columns of data. I'll run the pivot on a separate tab and then link the formulas to the main tab.... Just need to know how to combine a vlookup with an ID statement that will provide me with the totals for specific ranges that I identify... Hope this all makes sense. Thanks!!!

 

[etaf]

so the pivot table for the example would give

12c3 = 3
14d4 = 2

accounts within range
0-5 = 2
5.1-10 = 3
10.1+ = 0

is that correct -
the pivot does NOT give the account number
you could do that on the raw data if combined into a table with 3 columns and a 1 if between range

 

[BankBob]

Thanks Wayne. Yes, the pivot will give a count of the # of accounts by client ID. I then need a formula to show the # of accts within each specified range by client ID. For example, client ID #1 may have 4 accounts from 0 - 5, 15 accounts from 5 - 10 and 34 accounts over 10... Something like that. The # of rows of raw data is over 30,000 and there are more than 200 client IDs. Thanks!

 

[etaf]

in the raw data , try putting some formulas
you could have a column that specifies range
or 3 columns - depends on data results needed
which can then be added to pivot table and counted

=if( account cell <5 , "R5", IF( account cell <5 , "R10" , "R10-1" )) or something like that
then use that as a data label in the pivot table
if you use a table - when you update the table the formula should update to the new rows

OR new columns
R5 R10 R10+
and then do an IF ( account cell < 5 , "R5" , "")

just depends on how to display the results and raw data structure

can you link to a sample data file