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
<tbody>
</tbody>
Sheet 2: Activities
<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!
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!