Time Spent When Two Variables Are Present

leilah

New Member
Joined
Dec 5, 2017
Messages
4
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
How about ...
Code:
=SUMPRODUCT((Sheet1!A$13150:A$13944=G16) * (Sheet1!I$13150:I$13944="True Positive"), 
             Sheet1!C$13150:C$13944 - Sheet1!B$13150:B$13944)
 
Upvote 0
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
 
Upvote 0
How about posting an example of your data and the expected result?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top