Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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**

**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__

__Rows 16 through 20__

**Closing summary:**

Thanks!

Andrea

EE_Metrics_Sample.xlsx

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.

Names of books

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.

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).

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)

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.

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

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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]

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

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]

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

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

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

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.

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial