Microsoft Excel 2003 (full product)

I have 3 sheets each containing thousands of records in total. I am creating one file to count how many people from each group (there are up to 50) are working in a certain department and section (there are up to 500). I had tried the option of creating =COUNTIFs for every record, and this did work, but on the scale I need it for it would be useless. I had the idea of creating three dropdowns on this counter workbook. One for the group, one for the dept and one for the section, so that when i select the relevant group, section and dept, vb code will count the amount of people from that group in the selected dept and section. Its a massive thing, just thought id put it out there to see if anyone had any ideas. thanks!

Have you consider an Array formula using SUM? Example:

A1 contains a Drop Down list for the values in D1:D5

B1 contains a Drop Down list for the values in E1:E5

C1 contains a Drop Down list for the values in F1:F5This Array formula should count the number of times the combination of A1, B1 and C1 appears in D1:F5 -

{=SUM((D1:D5=A1)*(E1:E5=B1)*(F1:F5=C1))}

Do not enter the { }.

Enter the formula without them and then use Ctrl-Shift-Enter to make it an Array formula. Excel will add the brackets.

Each time you edit the formula, you will need to use Ctrl-Shift-Enter.

Cheers Derby,

the thing is, the values that this workbook needs to count are in 3 other seperate files. The way (wrongly) that I had this done before I looked at three dropdowns was doing a count for every section in ever department and every group, then getting the counter file to read the outcomes of this, and it work out that if i wanted to even do 6 depts and 6 groups, there were hundreds of counts to be donw, which took about 3 minutes to do which is far too slow for what I need it for. So i basically needs these dropdowns to, when selected, do the counting of the values on the relevant external sheet, then give me the data on that sheet.

Maybe I'm missing something... I was just giving you an example of how the array formula might work.

I created my Drop Downs in Book1 and then created Book2, Book3 and Book4 for my Group, Dept and Section data. I saved and closed Book2, 3 & 4.

My array formula looks like this, more or less:

{=SUM(('C:\Documents and Settings\user_name\Desktop\[Book2.xls]Sheet1'!A1:A5=A1)*

('C:\Documents and Settings\user_name\Desktop\[Book3.xls]Sheet1'!A1:A5=B1)*

('C:\Documents and Settings\user_name\Desktop\[Book4.xls]Sheet1'!A1:A5=C1))}Will that not work for you?

Derby

Let me re-explain. Each single book contains all of the variables, being Group, Section and Dept, rather than each part having a different section.

Example: The group is 'group1', the dept is 'dept1' and the section is 'sect1'. There can be up to 50 groups and up to 500 depts in total. Rather than have a count that is group1 up to group 50 in department 1-500, which would have potentially thousands of counts, I need to have a system where I can select the count, for example how many people in group 9 are in department 306 and section 9, rather than having to do every count possible and just picking out the result of this count.

How are each of these workbooks laid out? Is it something like this or something very different?

A B C D 1 Name Group Dept Section 2 Bob Group 1 Dept 1 Section 2 3 Sue Group 1 Dept 2 Section 2 4 Achmed Group 2 Dept 1 Section 1Once we know how the data is laid out in each workbook, it will be easier to come up with a solution.

If it

issomething like my example, the answer would be 1 for each query since there are no matching sets of 3, right?

Derby,

Yes that is how each of the books are laid out. I had believed that I would have to go and do a =COUNTIF for every variable of 6 groupd in 6 departments, which I did do, and it simply meant that excel froze for around three mins, and when we're dealing with 1000s of records, its a bit of a nightmare

I believe my suggested formula would still work, but it would just need to be expanded to work across all workbooks. I'll shorten the paths and split the formula just for presentation purposes. I’ll use the ranges I used in my example above. This has not been tested.

{=SUM(('C:\[Book2.xls]Sheet1'!B2:B4=A1)*

('C:\[Book2.xls]Sheet1'!C2:C4=B1)*

('C:\[Book2.xls]Sheet1'!D2:D4=C1))+SUM(('C:\Book3.xls]Sheet1'!B2:B4=A1)*

('C:\[Book3.xls]Sheet1'!C2:C4=B1)*

('C:\[Book3.xls]Sheet1'!D2:D4=C1))+SUM(('C:\[Book4.xls]Sheet1'!B2:B4=A1)*

('C:\[Book4.xls]Sheet1'!C2:C4=B1)*

('C:\[Book4.xls]Sheet1'!D2:D4=C1))}If you need to sum up 6 sets of "groupings" at a time, could you create 6 matching Drop Downs, use my formula 6 times, and then sum those individual counts?

Perhaps include a "phantom" entry in your lists so that they would sum to 0 when you don't need all 6 groupings.

Of course, with that much data, and complicated queries, it might make more sense to use Access or some other Database application.

Derby,

I think you're right, it may indeed take aonther programme, Im just getting N/A for every try. Anywho,

thanks for all your help

I don't know what would cause a #N/A error with the formula I suggested. If a value from your Drop Down wasn't found, the result would be 0, not #N/A. In any case, here is what I did:

I created 3 files with data in A1:C30000. Groups in A, Depts in B and Sections in C

I created Drop Downs in another workbook to select Groups in column A, Depts in column B, and Sections in column C. I created 6 sets of Drop Downs so I could count 6 "groupings" at a time.

I then used this formula in D1 to count a grouping in the other 3 workbooks:

{=SUM(([Book1.xls]Sheet1!$A$1:$A$30000=A1)*([Book1.xls]Sheet1!$B$1:$B$30000=B1)*([Book1.xls]Sheet1!$C$1:$C$30000=C1))+

SUM(([Book2.xls]Sheet1!$A$1:$A$30000=A1)*([Book2.xls]Sheet1!$B$1:$B$30000=B1)*([Book2.xls]Sheet1!$C$1:$C$30000=C1))+

SUM(([Book3.xls]Sheet1!$A$1:$A$30000=A1)*([Book3.xls]Sheet1!$B$1:$B$30000=B1)*([Book3.xls]Sheet1!$C$1:$C$30000=C1))}I then dragged it down to D6.

It takes less than 2 seconds to update all 6 values based on either a change to one of the drop downs or a change in the data.

I can't see your spreadsheet from here, so I can't tell you why it doesn't work for you.

Derby, you're a genius, it works fine! thanks so much.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History