troubleshooting Question

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

Avatar of Andreamary
Andreamary asked on
SpreadsheetsMicrosoft Excel
8 Comments2 Solutions86 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 8 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 8 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004