#### charliechaz

##### New Member

- Joined
- May 26, 2020

- Messages
- 9

- 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 drop-down 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 SOP-02659, 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 drop-down 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 SOP-02659, 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