Help with SUMPRODUCT, COUNTIF, COUNTIFS, INDEX, MATCH- WHAT TO CHOOSE???

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

Please fill in the results table with your expected results for at least the first three years. Do this twice: once for ONE Doc ID and once for ALL.

Maybe this will get you started in C2:

=MAXIFS(TableTrain8[Doc_Ver],TableTrain8[Doc_ID],$A$2,TableTrain8[Effective Date],">="&DATE($B2,1,1),TableTrain8[Effective Date],"<="&DATE($B2,12,31))

I would use pivot table to solve this question.

Since there a linked data in the file I can't show you.

If you go for that option, add a new file without linked data and a smaller dataset.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until you provide a link telling us where else you have posted this query.)

Administrative Note:

Sorry, but your post does not comply with Rule #6 of our Forum RULES:

Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction, then send a private message to them asking for clarification.

All Participants:

Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.

No further help to be offered until OP complies with this request.

Please comply with my request to post your crosspost in compliance with the forum rules. Failure to do so may result in a short time out and anyone from responding to this post. PLease comply with the few rules we have that you agreed to abide by when you joined this forum. If you are unfamiliar, please re-read those rules.

With pivot table and calculated field.

See the attached file.

Hi,
with helpers in H-O:

C2

Please Login or Register  to view this content.

D2

Please Login or Register  to view this content.

E2

Please Login or Register  to view this content.