We help IT Professionals succeed at work.
Get Started

Finding total # of minutes between two dates for multiple columns using partial text string match as part of criteria

83 Views
Last Modified: 2016-03-17
I am providing a sample spreadsheet to illustrate what I am currently doing and how I would like to improve it:

TAB CALLED "26_MAY_2016"
This spreadsheet is normally linked to specific columns in another excel workbook to pull in the essential data for dates that tasks were completed and number of minutes the task took in our book production process for each of the 3 types of books: CAP, GPH and RCAP. For the purposes of providing a sample spreadsheet, I have broken those links and am providing static data.

Column A
Names of books

Column B
This is a helper for Column A that trims the book names to the first 3 characters because I didn't know how to match a partial text string in my SUMPRODUCT formula, so that I could total by the three types of books: CAP, GPH and RCAP.
I want to delete this helper Column by improving my formula so that it matches the first 3 characters of the data in Column A.

Columns C through P
Date columns and minutes columns for the 7 book production tasks (there are a total of 30+ book production tasks, but for the purposes of this example I am just providing 7).

TAB CALLED "MAY_Results"
Rows 1 through 13
This is an 'intermediate' table of calculations to enable me, due to my limited excel expertise, to achieve the FINAL RESULTS (as shown in rows 16 through 20 on the same tab)
Rows 16 through 20
The FINAL RESULTS table is the desired output.
I would ideally like to bypass the above 'intermediate'  table (Rows 1 through 13) by obtaining a formula that achieves the results shown in the FINAL RESULTS table.

Closing summary:
The FINAL RESULTS table is linked to an external spreadsheet for my manager to review/access.
I'm looking for a 'no maintenance' solution in providing these metrics to my manager — once the formulas and links are in place, the numbers will update automatically without intervention by me.
If the formulas can reference the Volume column (column A) in the 26_MAY_2016 tab, thereby eliminating the necessity of the helper column called 'Vol_Trim' (Column B) in the same tab, that would be ideal.

Thanks!
Andrea
EE_Metrics_Sample.xlsx
Comment
Watch Question
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 2 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE