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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
The formula in post#7 gives the same answers as you said it should be, are you now saying those results are wrong?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

charliechaz

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

Attachments

  • By Minute.JPG
    By Minute.JPG
    84.3 KB · Views: 3
  • By Hour.JPG
    By Hour.JPG
    58.4 KB · Views: 3
  • Sheet 2 With Reading Rate.JPG
    Sheet 2 With Reading Rate.JPG
    69.4 KB · Views: 3

charliechaz

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

Attachments

  • By Minute fixed.JPG
    By Minute fixed.JPG
    98 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
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)))),IFERROR(SUM(INDEX(a,,4))*G$16/60,0))
where G16 has the rate in mins.
 
Solution

charliechaz

New Member
Joined
May 26, 2020
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)))),IFERROR(SUM(INDEX(a,,4))*G$16/60,0))
where G16 has the rate in mins.
YES!!! THAT WORKS! Thank You Fluff!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,737
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

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