PDA

View Full Version : Counting



Cjluke
05-22-2007, 09:02 AM
I don't think this is that hard in terms of some of the stuff I see on here, but its driving me crazy. The attached spreadsheet has a list in it for investment trades. I need to be able to to find the particular types of trades for each trader ID. So, if I needed to find information for trader 6, I would need a formula that would find all entries for "6" in the Trader ID column, and then I need the formula to find all the "P" entries in the Broker ID column that correspond with trader 6, and then all of the "10" entries in the Trade ID column that correspond with broker "P", and then the sum of all of the "10" entries.

What this corresponds to is the sum of all VRDN trades done through Broker P by Trader 6.

I will need to do this in many flexible combinations. Like if I wanted to find the number of "7" trades in the Trade ID Column that correspond with Broker N and Trader 2.

I tried using the following:


IF(B2:B40000=6,COUNTIF(C2:C40000,"F"),0)

However, this just ends up returning the total number of trades down with broker F. Any help would be appreciated. Thanks!

Bob Phillips
05-22-2007, 10:07 AM
.

Paul_Hossler
05-26-2007, 08:38 AM
Another approach to consider would be to use pivot tables. These are very powerful analytical tools in Excel, that allow a lot of user interactivity to drill down and look at data.

I wasn't exactly sure where you wanted to go, but the attached Zip has 3 sample PTs using the data. These could be a starting point.

If you weren't familiar with pivot tables, I think it's worth investigating.


Paul

rbrhodes
05-28-2007, 02:47 AM
Hi Cjluke,

Thought it was an interesting problem. Silly me <g>. It was.

"I don't think this is that hard in terms of some of the stuff I see on here, but its driving me crazy."

The If logic for handling the search factors wasn't too bad but generating an efficient way to handle the Do|Loops proved not-useful. So I simply put one in wherever needed (except for the all 5 and the single digit queries, etc.)

It may be a not-useful effort but it was fun for a day!

Cheers,

dr