asked on

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

byundt

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

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

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

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

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 Henson

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.

byundt

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.

Andreamary

ASKER

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