How can I count # of rows that match a criteria in multiple cells within the same row?

[GEUser]

Hello, I've been trying to figure this out on my own and had no luck. What I'm trying to do is sum up # of rows that match across three parameters - Course, Unit, and Activity #s.

I have two sheets with this information:
Sheet 1: Sections

Document No.	Course	Course Name	Unit	Unit Name	Activity	Activity Name	Section	Section Name
T1U1.A1.S1	1	Managing People and Projects	1	Responding to Problems	1	Dealing with Dilemmas	1	Business Dilemmas
T1U1.A1.S2	1	Managing People and Projects	1	Responding to Problems	1	Dealing with Dilemmas	2	Dealing with Dilemmas
T1U1.A1.S3	1	Managing People and Projects	1	Responding to Problems	1	Dealing with Dilemmas	3	Dealing with Business Dilemma
T1U1.A1.S4	1	Managing People and Projects	1	Responding to Problems	1	Dealing with Dilemmas	4	Asking for Advice
T1U1.A1.S5	1	Managing People and Projects	1	Responding to Problems	1	Dealing with Dilemmas	5	Core Values
T1U1.A1.S6	1	Managing People and Projects	1	Responding to Problems	1	Dealing with Dilemmas	6	Problems and Solutions
T1U1.A1	1	Managing People and Projects	1	Responding to Problems	1	Dealing with Dilemmas		-
T1U1.A2.S1	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	1	Common Problems at Work
T1U1.A2.S2	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	2	Problems in Companies
T1U1.A2.S3	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	3	Problem Solving
T1U1.A2.S4	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	4	Commonly Confused Words
T1U1.A2.S5	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	5	Describing Problems and Solutions
T1U1.A2.S6	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	6	Identifiying and Solving Problems
T1U1.A2.S7	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	7	Expressing Necessity
T1U1.A2.S8	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	8	Making Suggestions

<tbody>
</tbody>

Sheet 2: Activities

Document No.	Course	Course Name	Unit	Unit Name	Activity	Activity Name	# of Sections
T1U1.A1	1	Managing People and Projects	1	Responding to Problems	1	Dealing with Dilemmas
T1U1.A2	1	Managing People and Projects	1	Responding to Problems	2	Problem Solving	-
T1U2.A1	1	Managing People and Projects	2	Business Plans and Processes	1	Adapting to an Evolving Business	-
T1U2.A2	1	Managing People and Projects	2	Business Plans and Processes	2	Making Business Plans	-
T1U3.A1	1	Managing People and Projects	3	Improving Business Performance	1	Analyzing a Company's Organization	-

<tbody>
</tbody>

In both sheets, Document No. is concatenated from Course, Unit, and Activity columns.

What I'm trying to do is count the # of sections in each activity, which is the number of rows in sheet 1 that match the Course/Unit/Activity #s in Sheet 2.

There are lot of sections (1000+ rows) and a lot of activities (100+) that they belong to, so I'd rather not select the rows using ROWs function manually.

Is there a combination of functions that could do this automatically? I've been searching across Forums reading about SUMPRODUCT, ROW, ROWS, IF and COUNTIF functions, but not able to find a solution that works. To me it seems so simple, but I just can't figure out the right code to compare the two sheets.

Down the line, I also would like to populate Sections sheet (Sheet 1) with Course Name, Unit Name and Activity Name automatically from Activities Sheet (Sheet 2). I think I would need to do a complex VLOOKUP code, but again, how do I refer to multiple matching cells, so that all three values in the same row match (Course #, Unit #, and Activity#)?

Thank you in advance for you help!

 

[frank_AL]

GEU,

Enter this formula in cell H2 of Sheet 2.
=COUNTIFS(Sheet1!B2:B16,Sheet2!B2,Sheet1!D2:D16,Sheet2!D2,Sheet1!F2:F16,Sheet2!F2,Sheet1!H2:H16,">0")

That formula currently only evaluates Rows 2-16 per your example. You would need to expand that to encompass all rows you are trying to evaluate.
The last evaluation: H2:H16,">0" I added since there was a 7th row that matched the first three equations but there was no number in the Section Column so I assumed you didn't want to count that row.
You can delete that from your formula if you want to count that row.

 

[frank_AL]

GEU

I made one mistake. At a minimum, the rows on these Sheet1 ranges must be set to absolute to maintain the integrity of the Ranges when you copy the formula down to other rows.
In the updated formula below I set both Column and Row as Absolute.

=COUNTIFS(Sheet1!$B$2:$B$16,Sheet2!B2,Sheet1!$D$2:$D$16,Sheet2!D2,Sheet1!$F$2:$F$16,Sheet2!F2,Sheet1!$H$2:$H$16,">0")

 

[GEUser]

Thank you for the response frank_AL! Not sure why, but this formula returns a value of 0.

I want to post for all a solution offered by my colleague at work:
Step 1) Insert a new column and create a concatenated code in Sheet1 just for the activity # ("a key").
Step 2) Create a pivot table (in a separate sheet) to count all instances of the key in Sheet1,
Step 3) Paste Pivot table values into Sheet 2 (preferably just the values, not the formula).
Step 4) Delete unnecessary sheets and columns once the calculation is complete for all rows.

It takes a few steps, but did achieve the intended result.