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]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with SUMPRODUCT, COUNTIF, COUNTIFS, INDEX, MATCH- WHAT TO CHOOSE???
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

For the individual documents, how about

=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!$B$2:$B$1000,(Sheet1!$C$2:$C$1000>=DATE(B2,1,1))*(Sheet1!$C$2:$C$1000<=DATE(B2,12,31))*(Sheet1!$A$2:$A$1000=$A$2)))),0)

 

[Fluff]

This will handle the All as well

=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!$A$2:$B$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))))),0)

 

[charliechaz]

This will handle the All as well

=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!$A$2:$B$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))))),0)

Thank you Fluff! The second formula (with "All") worked like a charm for the first question, Count of Revisions. I then modified the Filter array to Sheet1!$E$2:$E$1000 and found that this answered the second question, Total Training Events. The reason I know it worked, is that I had performed a manual count by filtering and counting the data set. I thank you sir!!!

Now if I can only figure out the third question, Total Training Time. I performed this calculation manually by performing the following steps:

1. On sheet1, I filtered by Doc ID, then by Effective Date (In the filter I selected a year).
2. On the filtered set, I added the 'Pages' (sum of pages) and multiplied the result by the total count of users (which equate to count of records), divided by 60. (sum of pages * count of users/60). Note: In my original post, I had stated that the calculation should be based on 0.2 hrs/page, but the calculation above brings it to 0.25 hrs/page.

Any ideas?

 

[Fluff]

Thanks for that.
I don't understand how you get an answer of 221.25 for All in 2015. The sum of pages is 732 with 45 users which is 549.

 

[Fluff]

Think I've got it, 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)))),u,UNIQUE(INDEX(a,,1)),t,--(TRANSPOSE(INDEX(a,,1))=u),SUM(MMULT(t,INDEX(a,,4))*MMULT(t,SEQUENCE(COLUMNS(t),,,0))/60))

 

[charliechaz]

Hi Fluff. You're right. At this point, I believe that the base for this calculation should be 3 minutes per page. So if my math is right, 732 pages at 3 minutes per user will equal 36.65 hours. It would be nice if there was another helper cell maybe called 'Rate' where the 3 minutes can be established for the calculation. If someone in my department thinks differently, it can be adjusted here.
So in the end the formula would look for unique records composed of Doc_ID, Effective Date and User then filter the year then count the amount of unique users and multiply by the total sum of pages. Did I get this right?

 

[Fluff]

Not sure what you mean by 3 minutes per page, there is nothing in any of the formula that refer to that.

 

[charliechaz]

Not sure what you mean by 3 minutes per page, there is nothing in any of the formula that refer to that.

This all boils up to Total Training time (Sheet 2, E2)