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

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

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

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

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.

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.

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