charliechaz
New Member
 Joined
 May 26, 2020
 Messages
 7
 Office Version

 365
 2016
 Platform

 Windows
Been scratching my head for the last week and can't figure out my solution.
I have a workbook with 2 sheets. Sheet 1 is Training Data. It lists documents, document versions, effective dates, pages and users.
In Sheet 2, I want to collect count data so I can provide charts, etc.
I have created a drop down with unique Doc_ID values from column A of sheet 1 and added a value of "All". If "All" is selected, I'd like all the formulas to perform the calculations below for all document versions.
Formula in column G UNIQUE(Sheet1!A2:A810,FALSE) I added "All" and hid it. I also created a dropdown in A2 via Data Validation.
I'm looking for 3 formulas for Sheet 2:
1. Cell C2 Count of revisions. Here, I want to count the number of times a doc has been revised, (Doc_Ver) per Doc_ID. Example: There are 5 rows for SOP02659, version 1.0. This would be a count of "1". Version 2.0 has 6 rows. This will be count # 2 and so on. There are many doc_ids with many versions. This is where the helper cell A2 comes in play. I started with COUNTIF(A2:A88,F2) but when trying to incorporate a date SUMPRODUCT((YEAR($C$2:$C$88)=B2)) it doesn't work.
2. Cell D2 Total Training Events This is the count of how many unique users trained on each Doc_ID and Doc_Ver. Note: the users in column F are made up to protect their names. There are possible duplicate user names per doc_ID and Doc_Version, hence "unique".
3. Cell E2 Total Training Time This is a calculation of the total hours spent on training per Doc_ID and Doc_Ver. Each user spends 0.2 hours per page. I assume the training events per Doc_ID and Doc_Ver multiplied by Pages.
Any help is appreciated
I have a workbook with 2 sheets. Sheet 1 is Training Data. It lists documents, document versions, effective dates, pages and users.
In Sheet 2, I want to collect count data so I can provide charts, etc.
I have created a drop down with unique Doc_ID values from column A of sheet 1 and added a value of "All". If "All" is selected, I'd like all the formulas to perform the calculations below for all document versions.
Formula in column G UNIQUE(Sheet1!A2:A810,FALSE) I added "All" and hid it. I also created a dropdown in A2 via Data Validation.
I'm looking for 3 formulas for Sheet 2:
1. Cell C2 Count of revisions. Here, I want to count the number of times a doc has been revised, (Doc_Ver) per Doc_ID. Example: There are 5 rows for SOP02659, version 1.0. This would be a count of "1". Version 2.0 has 6 rows. This will be count # 2 and so on. There are many doc_ids with many versions. This is where the helper cell A2 comes in play. I started with COUNTIF(A2:A88,F2) but when trying to incorporate a date SUMPRODUCT((YEAR($C$2:$C$88)=B2)) it doesn't work.
2. Cell D2 Total Training Events This is the count of how many unique users trained on each Doc_ID and Doc_Ver. Note: the users in column F are made up to protect their names. There are possible duplicate user names per doc_ID and Doc_Version, hence "unique".
3. Cell E2 Total Training Time This is a calculation of the total hours spent on training per Doc_ID and Doc_Ver. Each user spends 0.2 hours per page. I assume the training events per Doc_ID and Doc_Ver multiplied by Pages.
Any help is appreciated