SUMPRODUCT with ID numbers that has minus signs and using time as an array...

[excellenthelp]

Excellers,

How may I write two formulas with SUMPRODUCT below?

ID	Start Time	End Time
13-01294-001	5:00	7:00
13-01294-002	12:00	1:00

After the SUMPRODUCT is calculated, the result should show 3:00 (3 hours).

I know there is a simple way to write this... but I do not know how!

Attached is a sample workbook to play around with.
SampleHelp.xlsx

Many thanks!!

[ConneXionLost]

Given you've already provided the helper columns, you might try this:

Please Login or Register  to view this content.

Cheers,

[excellenthelp]

ConneXionLost,

The SUMIF formula you gave works just fine, but I want to use the A column instead of B... I just have the B column as a sample to compare the data to see if it comes out correctly.

Just use columns A, C and D as stated in the sample workbook attached in the first post above.

Thanks for your willingness to help!

[Richard Buttrey]

Hi,

I think you're over complicating this. Neither SUMPRODUCT() nor the more efficient SUMIFS() which effectively replaced it when 2007 was introduced are needed.

Your current 'hours' column is actually holding time numbers, i.e. a decimal of 24 hours.

In E2 enter the formula

Please Login or Register  to view this content.

then copy this down.

Now you can use a simple SUMIF()
i.e.

Please Login or Register  to view this content.

and importantly format this cell as
[h]:mm

[excellenthelp]

Richard,

This provides clearer solution and right on the point.

However, as you said, I was over-complicating this. I have simplified the workbook to what exactly what I want to use. I want to do a kind of SUM (SUM, SUMIF, SUMIFS, SUMPRODUCT- whichever is the best) for both 13-01294* and 13-00842*.

Attached is the revised workbook.
SampleHelpV1.xlsx

[Richard Buttrey]

Hi,

See attached