Excel Help.... please

[baldgriff]

So Im having to do more Excel work than I have done in probably 8 years. Anyway, I have a spreadsheet that lists Account Number, Inv Symbol and Market Value.

I want to get a count of each accounts different holdings and a total for all of the holdings combined. Unfortunately, I don't have Access to dump this into and spit out what I am looking for so I am wondering how to do this in Excel.

Anyone want to provide me with some help to accomplish this task... I will ask the Bar Tender to get you a drink of your own (on Underhill's tab as always)

Thanks in advance.

[chancellor]

baldgriff - I'm assuming each Account Number has multiple Inv Symbols and Market Values. If I have this right, do a Data/Sort on Account Number, with a secondary on Inv Symbol. That will line up your account numbers in order and put the Inv Symbols in order with each account number.

I'd then insert a cell to subtotal each Account Numbers Market value. You can sum all the subtotals in a cell at the bottom, or anywhere else you'd like to in the spreadsheet.

Hope that's clear - if not, I'll try to create a mini-version and post it.

[Sklargazer]

Either pivot table or count if formula should do the trick. If you're just looking for count totals, I think pivot is your answer.

[heyelander]

Yep, pivot tables

[baldgriff]

Thanks!

Next problem. I am trying to analyze this a bit. I have the table giving me a count of the number of funds in an account and the value.

So acct X has a market Value of $$$ with a total of Y Funds (then detail in rows below)....

I want an easy way to know how many accounts have more than 20 Funds, how many 15 - 19, how many 10 - 14. Is there a way to get this without manually counting this?

[Controller Jacobs]

Thanks!

Next problem. I am trying to analyze this a bit. I have the table giving me a count of the number of funds in an account and the value.

So acct X has a market Value of $$$ with a total of Y Funds (then detail in rows below)....

I want an easy way to know how many accounts have more than 20 Funds, how many 15 - 19, how many 10 - 14. Is there a way to get this without manually counting this?

For the range A1:A10, here is the count of values between 1 and 5:
=COUNTIFS(A1:A10,">=1",A1:A10,"<=5")

Some notes:
- make sure you have the double quotes around the comparisons (e.g. "<=5")
- for a single comparion ( like a simple < 5), you use COUNTIF

[baldgriff]

Thanks Controller!!!

My last question for now...... I have multiple account ranges and have been able to exclude one of the sets (D8), but I am having difficulty filtering out both D8 and N1. There are 5 other ranges that I want included, but those 2 I don't want. Im using a label filter to sort out D8, but I cant seem to filter both ranges out. Any ideas??


By the way Underhill is still buying!