Counting with multiple criteria

[TimDroz]

I am trying to count values in one column if there is a value in the same row in another column. Complicating things, the count is in a separate worksheet in the same workbook. Sorry in advance if this explanation is confusing, the tables themselves contain some sensitive data that I can't publish, so I have to try to be careful about what I disclose.

I have a spreadsheet that consists of a report worksheet and numerous other worksheets (all based on a common template but containing different data) that feed the report.

The report is a summary of data from a number of other worksheets (all in the same file). Each row in the report corresponds to a different worksheet. The first column of the report contains the names of all the tabs, and I'm using INDIRECT to key the formulas in all the other columns, which basically just tabulate some counts, like so:

Please Login or Register  to view this content.

In this example, B4 is the first cell of the first row in the formatted table, which contains the name of the first data worksheet, and the range T9:T500 contains a list of dates corresponding to when the person indicated in a different column in that row performed a particular task. The result (which works fine) is a count of all the cells that contain a date, indicating that the task is complete (as opposed to an empty cell, which is not counted, and means that the task is not complete). From this I can determine how many lines (tasks) are complete.

The last feature of the report I would like to incorporate correlates the value in one column (a department name) with the date previously mentioned. In plain English, the correlation means to me that, if there's a date in the last column, a person completed that task on behalf of the department. Put another way, there are half a dozen or so departments, and I'm trying to get a count, by department, of tasks that have been completed. I can already count the breakdown of tasks by department, but I need to now figure out by department how many are complete (and by inference, how many are incomplete).

So if I have the following sample data, how would I go about creating a formula that increments the count based on these criteria:

1. If the value in column N is "ABC" then...
2. If the value in column T of the same row is not empty, increment the count.

Column N:
N1 Human Resources
N2 Human Resources
N3 Tech Support
N4 Human Resources
N5 Tech Support
N6 Customer Billing

Column T:
T1 -empty-
T2 5/17/2010
T3 -empty-
T4 5/20/2010
T5 -empty-
T6 6/29/2010

So, for instance, I need to be able to specify "Human Resources" as the match, and I would expect it to return a count of "2" since there are two entries in Column T that are not empty that correspond to "Human Resources" in Column N.

If someone is able to help me figure out how to just do the counting, I should be able to work backwards to incorporate the worksheet references using INDIRECT and all that stuff. I've tried using combinations of COUNT, IF, COUNTIF and VLOOKUP but all I can ever get it to do is evaluate to 1 or 0, not a count.

There may be an easier way to do this with pivot tables, but I haven't been able to figure out how to get that working correctly either.

Thanks in advance for any help.

Tim

[NBVC]

Try maybe:

=IF(B4="","",SUMPRODUCT(--(INDIRECT("'"&$B4&"'!$N9:$N500")="Human Resources"),--(INDIRECT("'"&$B4&"'!$T9:$T500")<>"")))

[TimDroz]

Brilliant! It works!

Thanks so much. Saved me probably days of beating my head against it.