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

[charliechaz]

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

 

[Fluff]

The formula in post#7 gives the same answers as you said it should be, are you now saying those results are wrong?

 

[charliechaz]

The formula in post#7 works as requested. However, upon further analysis the Total Training Time should be calculated at 3 minutes per page. For example, if we filter to Doc ID SOP-02659 and Effective Date = 2015, we see that the sum of pages = 189. If we calculate Sum of Pages * number of records/60 we get 47.25. Again, the formula in post#7 works and it's what I asked for, but it is based at 15 minutes per page, which in my opinion is not realistic. This initial calculation was manually done by one of my colleagues but I believe it is not accurate.
Per this calculation, it means that there are a total of 2835 minutes, divided by 60 = 47.25 hours. If we base it on 3 minutes/per page we'll see a total of 567 minutes, divided by 60 = 9.45 hours. By the way, 3 minutes = 0.05 hours and 15 minutes = 0.25 hours. I mention this as these numbers avoid the division by 60.

I have added a helper cell in Sheet2 called 'Reading Rate'. The values are in minutes or in hours and depending on the resolution of the reading rate question with my colleagues. That's where this helper cell comes in handy. If 'minutes' is chosen, I think the formula has to include /60, whereas selecting 'hours' is not necessary.

 

[charliechaz]

The formula in post#7 works as requested. However, upon further analysis the Total Training Time should be calculated at 3 minutes per page. For example, if we filter to Doc ID SOP-02659 and Effective Date = 2015, we see that the sum of pages = 189. If we calculate Sum of Pages * number of records/60 we get 47.25. Again, the formula in post#7 works and it's what I asked for, but it is based at 15 minutes per page, which in my opinion is not realistic. This initial calculation was manually done by one of my colleagues but I believe it is not accurate.
Per this calculation, it means that there are a total of 2835 minutes, divided by 60 = 47.25 hours. If we base it on 3 minutes/per page we'll see a total of 567 minutes, divided by 60 = 9.45 hours. By the way, 3 minutes = 0.05 hours and 15 minutes = 0.25 hours. I mention this as these numbers avoid the division by 60.

I have added a helper cell in Sheet2 called 'Reading Rate'. The values are in minutes or in hours and depending on the resolution of the reading rate question with my colleagues. That's where this helper cell comes in handy. If 'minutes' is chosen, I think the formula has to include /60, whereas selecting 'hours' is not necessary.

On the 'By Minute' image, please ignore the *#records on the last 2 columns. Please see the corrected image

 

[Fluff]

How about

=LET(a,UNIQUE(FILTER(Sheet1!$A$2:$E$1000,(Sheet1!$C$2:$C$1000>=DATE(B2,1,1))*(Sheet1!$C$2:$C$1000<=DATE(B2,12,31))*(IF($A$2="All",1,Sheet1!$A$2:$A$1000=$A$2)))),IFERROR(SUM(INDEX(a,,4))*G$16/60,0))

where G16 has the rate in mins.

 

[charliechaz]

How about

=LET(a,UNIQUE(FILTER(Sheet1!$A$2:$E$1000,(Sheet1!$C$2:$C$1000>=DATE(B2,1,1))*(Sheet1!$C$2:$C$1000<=DATE(B2,12,31))*(IF($A$2="All",1,Sheet1!$A$2:$A$1000=$A$2)))),IFERROR(SUM(INDEX(a,,4))*G$16/60,0))

where G16 has the rate in mins.

YES!!! THAT WORKS! Thank You Fluff!!

 

[Fluff]

Glad to help & thanks for the feedback.