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

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
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Andrea,
Your Table layout is not geared towards an efficient formula.

If you are willing to put all the Col columns together and all the Min columns together (as shown in attached workbook), then you can use the following formula for your totals:
=SUMPRODUCT((Table1[[Col1]:[Col7]]>=B$16)*(Table1[[Col1]:[Col7]]<=B$17)*(Table1[[Min1]:[Min7]])*(LEFT(Table1[[Volume]:[Volume]],3)=LEFT($A18,3)))

The above formula may be copied across and down. All auxiliary columns may be deleted with this formula, including Table1[Vol_Trim] and rows 1:13 on worksheet May_Results.

Brad
EE_Metrics_SampleQ28933655.xlsx
byundtMechanical EngineerCommented:
Andrea,
Although it is more efficient calculation-wise to rearrange your Table, if the counts in the MIN columns will never be in the 40,000 range (and thereby be confusable with dates), you can use the following formula without rearranging your table:
=SUMPRODUCT((Table1[[Col1]:[Col7]]>=B$16)*(Table1[[Col1]:[Col7]]<=B$17)*(LEFT(Table1[[Volume]:[Volume]],3)=LEFT($A18,3)),Table1[[Min1]:[Min7]])

There are two key differences compared to the previous formula.
1. The formula is evaluating twice as many columns because alternating Col and Min columns mean that you perform a date test on many of the Min columns, and multiply by many of the Col columns. You still get the right answer if the values in the Min columns can never be confused with valid dates because they are all less than 40,000.
2. I had to move the Table1[[Min]:[Min7]] bit to the end of the SUMPRODUCT and precede it with a comma instead of an asterisk. Preceding with a comma means that any text values in that range are ignored. When I used an asterisk, the formula returned #VALUE! error value because the "blank" cells in Col columns in Table1 actually contained empty strings (look like blanks). And a number times an empty string returns #VALUE!

I can also use the above SUMPRODUCT formula with the rearranged Table1. Rearranging the Table makes the calculation more efficient and bullet-proof. Once that has been done, there is no additional efficiency advantage for the new formula compared to the previously suggested one.

Brad

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Alternative arrangement of the table would be to arrange with the following columns:

Volume
Trim
Stage
Date
Time

You can then setup your results table with SUMIF(S) functions or SUMPRODUCTS as suggested above. You might even be able to use a Pivot Table. Multiple SUMIFS or SUMPRODUCTS can be quite resource intense whereas a Pivot is a one off resource hit each time it is generated or refreshed rather than resource use every time the workbook calculates.

Thanks
Rob
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Rob HensonFinance AnalystCommented:
See attached with my suggestion.
Copy-of-EE_Metrics_Sample.xlsx
AndreamaryAuthor Commented:
Thanks very much, Brad, for your detailed explanation and solution options...much appreciated! Since the MIN columns will never be in the range of 40,000 I will go with your second solution, but if performance becomes an issue then it's good to know how I can make it more efficient and bullet-proof. BTW, how do I tell if a cell is not a 'blank', but actually contains an empty string? And if other columns in the future happen to contain empty strings, I take it the formula would be to be revised accordingly?

Rob, thank you for your suggestion and sample spreadsheet. As my preference was to eliminate the 'Trim' column + avoid having to refresh the data (as I assume would be required for a pivot table) but instead have a 'no maintenance' solution in case I'm away on holidays, etc. I didn't opt to go this route, but appreciate your input.

Cheers,
Andrea
Rob HensonFinance AnalystCommented:
If the data is set as a Table (rather than a standard list) the TRIM function can be "installed" in the table and will be populated as new data is entered. The pivot is linked to the table so the source data range will update as data is entered. The summary would then be updated with a simple right click and select refresh.
byundtMechanical EngineerCommented:
How do I tell if a cell is not a 'blank', but actually contains an empty string?
I was able to figure it out by applying the logic used in The Hound of the Baskervilles. SUMPRODUCT always returns #VALUE! error when you multiply text by a number. It usually returns a numeric answer when you use a comma to separate a SUMPRODUCT parameter that might contain a mix of text and numbers. Even though I couldn't see the text values, Sherlock Holmes would have instantly deduced their presence by the absence of a numeric result.

Being quite a bit duller than Mr. Holmes, I used the Formulas...Evaluate Formula menu item. After clicking the Evaluate button 11 times in a row, I saw a mix of empty strings and dates (in the Table1[[Min1]:Min7]] expression in the Evaluate Formula dialog. And when I clicked the Evaluate button once again to perform the multiplication, that mix turned into a jumble of #VALUE! errors and numbers.

Given this inkling that text was afoot, I dutifully examined the cell contents in the formula bar. No text. No apostrophe. No shift of the cursor in the formula bar when I hit the Backspace key. Arghh!

Still following the clue like a bloodhound, I used the F5 Special Cells menu item to count the number of blank cells in the range. Even though I could see blank cells with my own two eyes, F5 Special Cells told me that there weren't any.

Having treed the rascal, I confirmed his guilt using the ISBLANK function to test the "blank" cell in question. It returned FALSE with the clap of a verdict in the Old Bailey. An invisible empty string it was, the Excellian consequence of one range containing formulas returning empty strings being delivered via Paste Special...Values into another.

And if other columns in the future happen to contain empty strings, I take it the formula would be to be revised accordingly?
Actually, you should be able to leave the formula as-is. The other pieces of the SUMPRODUCT are all Boolean expressions. They are usually quite tolerant of text, especially the way they are constructed in the suggested formula.
AndreamaryAuthor Commented:
Thanks, Rob, for elaborating on the solution.

And Brad...thank you for the most delightful comment/explanation I have had the pleasure of reading on this website. :-)

Cheers,
Andrea
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.