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

BankBob

New Member
Joined
Jan 4, 2019
Messages
8
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!!!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top