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

charliechaz

New Member
Joined
May 26, 2020
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. 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
 

Attachments

  • excel_sheet1.JPG
    excel_sheet1.JPG
    82.2 KB · Views: 23
  • excel_sheet2.JPG
    excel_sheet2.JPG
    39.5 KB · Views: 23

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
For the individual documents, how about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
This will handle the All as well
Excel Formula:
=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

New Member
Joined
May 26, 2020
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This will handle the All as well
Excel Formula:
=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?
 

Attachments

  • excel expected results.JPG
    excel expected results.JPG
    112.1 KB · Views: 6

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
Think I've got it, how about
Excel Formula:
=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

New Member
Joined
May 26, 2020
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
Not sure what you mean by 3 minutes per page, there is nothing in any of the formula that refer to that.
 

Forum statistics

Threads
1,181,615
Messages
5,930,962
Members
436,767
Latest member
Langaws

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top