Counting and summarising data

gh

New Member
Joined
Oct 22, 2002
Messages
5
I have a workbook which records the results of test cases across various lines of business. Each line of business has it's own worksheet with test case numbers, description, results etc.

As each tester completes a test, they put their initials against a test case. It's these initials that I'm trying to count. I need to find the total number of test cases that tester has completed over all lines of business (a range of worksheets) and summarise.

I've written a small macro which determines the individual testers in a worksheet (for example, there were 3 testers who worked on a particular line of business, their initals being ab, cd and ef) but I can't figure out how to do this across multiple sheets (determine how many testers worked on the entire project and what their initials were) and then produce a count of all the test cases they initialed over all sheets.

All initials are in column C of each worksheet. As more testers may be brought in to test over the length of the project, I need to keep it dynamic, rather than hard coding the tester intials, which is why my initial macro looked promising.

I'd be grateful for any advice you may have.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Sounds to me as though you will need a For Each loop.

First step will be to take you macro and when it finds a tester's initials add them to a list (if not already on there).

Then your for each loop will go through each tester in that list and collate the data from the various sheets.

If you mail me a small sample I can set something up which you can then use to write the full macro.

Thanks
Luke
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hello: gh
Welcome to the Board

A short sample of your data and your expected result would be helpful to be posted on this forum as it is a learning experience who monitor this forum.

I am sure you will get a variety of possile answers.

You can use the utility shown under this message box.

pll
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
The morefunc add-in has a 3D COUNTIF which can be used like this...

=COUNTIF.3D(Sheet1:Sheet10!A2:H80,"ab")

Might be useful in tackling your problem.
 

gh

New Member
Joined
Oct 22, 2002
Messages
5
Thanks for all the responses so far. You've been most helpful.

Luke: your idea for a For-Each loop based on the contents of the array was just the ticket. I've got a working version which summarises a single worksheet quite nicely... more on this in a bit.

pll: I tried to use Colo's utility, but couldn't get it to work. If at first you don't succeed, and all that. :)

Aladin: The COUNTIF.3D solution would be quite elegant if it was just me that was collating this information. As it is, a number of team leads are also looking at this macro with interest and I'd rather provide them with a solution that doesn't require third-party plug-ins. Good suggestion though.

I have one more question. As I mentioned above, I got the macro to work for a single worksheet using a named range. The requirements for the macro have changed slightly, and now I have to report totals for each line of business (worksheet). If the workbook has 12 sheets, is it possible to use a for-each loop to step through a subset of the sheets, eg. 5-10? I noticed that COUNTIF.3D could specify a 'range' of worksheets; is there some other way to do this?

I'm not one to wait for a solution to land in my lap as I see this as a valuable learning experience. It's been a while since I did any programming and I'm finding this quite fun. :)

Thanks in advance.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You can certainly run a For Each Loop for the sheets - again you need to set up list of sheets so it knows where to go...

Then embed the other Loop within this loop

So

For Each Sheet

Tester Loop

End Tester Loop

Next Sheet

etc...
 

Forum statistics

Threads
1,144,150
Messages
5,722,796
Members
422,459
Latest member
Chriselff

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
Top