Count # of times a value occurs in a given field across an indefinite # of sheets

Please see the attached workbook. The first page "Summary Log" is intended to automatically summarize how many calls occur for each department. I would like for Excel to look at each "Call Sheet" (which can be an indefinite number of sheets), identify the value in the "Department" field, and create a count of how many times that department occurs. For instance, if there were 12 "Call Sheet"s, and the "Department" field in 6 of those sheets had "Merchant" selected, I would like for cell F12 on the "Summary Log" to provide a value of 6.

If anyone has tips or suggested readings on how to make this happen, I would greatly appreciate it. My grand idea is to have a macro written so that these summary counts would automatically populate every time a "Department" field is changed in any of the created "Call Sheets."

First time poster, so please be gentle.

Thank you in advance for your time!

I think a User Defined Function is in order for this. Add this UDF into a standard module, like your Module3:

Please Login or Register  to view this content.

Now, on the Summary Log, put this formula in F10:

=COUNTDEPT(C10, "I12")

The first parameter is the cell to "match", in this instance the categories listed in column C.

The second parameter is cell address you want to lookin on all the other sheets. This is a string, so encase it in quotes as shown). We are looking cell I12 on the other sheets to see what the value is.


Now copy F10 down through F14 and your totals will appear.



Here's an edit to your AddCall macro, too:

Please Login or Register  to view this content.

If you tell me exactly what the NameTime is supposed to do verbally, we can probably trim that down a bit, too.