Function to count values from one range if value exists in other range

[DougTucker]

Hi All:

I'm working on an Excel spreadsheet where I need to compute values in several hundred cells, and the formula will vary depending on the values in the first few columns.

I think the best way to accomplish this is through a formula that would spin through each range. Here's the general concept:

Count the number of occurrences of [Value1] in [Range1]
WHERE [Value2] is in [Range2].

Here's a summary of what the spreadsheet looks like:
Column D contains the name of a person assigned to a process.
Columns K through are weeks of the year.
A different process (A, B, C, D) occurs in each week.
I need to sum up the number of times each person has activities in in each process for any given week. The person will be listed multiple times in column D (which makes up [Range2]).

[Value1] is a process code (A, B, C, or D) in [Range1] (K3:K13)
[Value2] is a person's name, in [Range2] (D313)

The result will be saved in a summary grid in cells K15:K18 (which are the totals for process steps A, B, C, & D.

This process will be repeated for every column (K-AU). A summary grid will be created for each person (approx. 5 people total).

This will give me the number of times [Value1 exists in [Range1]:
=COUNTIF(K3:K13,E15)
Or a more complex version (using an array):
{=SUM(LEN(K3:K13)-LEN(SUBSTITUTE(K3:K13,E16,"")))}

I only want to sum the numbers if (for each row in [Range1]) the value in column D is equal to the value in D15.

I apologize for the complexity of this. I'm not able to attach a spreadsheet given the internet filters where I work.

Thanks in advance for any help or suggestions.

 

[SkipVought]

Please post a concrete example and the result you expect/

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}