Time Spent When Two Variables Are Present

[leilah]

Hello Forum,
We are trying to determine the minutes spent for a number of rows per device, but only when the minutes spent is categorized as a "true positive."

So our logic looks like this:
add minutes if (device name) and (true positive) then subtract (start time) from (end time)
device name = G16
true positive = sheet1!column_I
start time = sheet1!column_c
end time = sheet1!column_b

based on the assistance already received on a posting from Mr Excel as well as an article that touches on parts of this we came up with:
=SUMPRODUCT(((Sheet1!A$13150:A$13944=G16)+(Sheet1!I$13150:I$13944="True Positive")),((Sheet1!C$13150:C$13944)-(Sheet1!B$13150:B$13944)))

...but this seems to not actually perform the way we thought.

We are hoping to get the total minutes spent on a device when it is a true positive

any advice?

thank you in advance

Leilah

 

[shg]

How about ...

=SUMPRODUCT((Sheet1!A$13150:A$13944=G16) * (Sheet1!I$13150:I$13944="True Positive"), 
             Sheet1!C$13150:C$13944 - Sheet1!B$13150:B$13944)

 

[leilah]

thank you for the effort - unfortunately this is not working - the cell is reporting zero minutes spent

I know the total amount spent for the the device named in G16 equals 87.6

We also know there are 24 True Positives and 164 False Positives and there is definitely time spent in the True Positives category

In case this matters, the start and end times are formatted as date and time like this:
11/10/17 16:50

I am really not sure why this is failing.

Leilah

 

[shg]

How about posting an example of your data and the expected result?