Sum IF Problem/Puzzle

[Tmatti92]

Hi everyone,

So I'm having a bit of a headache trying to solve out this calculation/puzzle. Basically I am trying to SUM a range(Duration) IF the Screen name is "Test report screen" and IF the test ID is unique and the user ID is unique. The issue is this is becoming quite labour some as there are around 721 Tests and if I attempt to do this Uniquely you can imagine this will take some time. In screen shot 2 what i have done is add the Date; User ID; Test ID and removed the duplicates so I have an understanding of how many users there are and how many tests they have conducted per day.
Ideally in the last column in screen shot 2 "Test report sum" i just want to get the time value of that calculation with all the conditions stated above.

Hope this makes sense? Ideally id just like to be pointed in the right direction. Maybe I tackled this the wrong way with the setup of the spreadsheet or I need to write an efficient formula. Let me know if you have any questions and I can gladly answer

Kind regards,
Thomas

 

[jasonb75]

I am trying to SUM a range(Duration) IF the Screen name is "Test report screen" and IF the test ID is unique and the user ID is unique.

Is that totally unique, or unique as a combination?

Something that comes to mind is to work on the basis of

=IF(COUNTIFS(Table1[Screen Name],"Test report screen",Table1[Test ID],C2,Table1[User ID],B2)=1,SUMIFS(Table1[Duration],Table1[Screen Name],"Test report screen",Table1[Test ID],C2,Table1[User ID],B2),"Not Unique")

 

[Tmatti92]

Hi Jason,

Cheers for the response,

Unfortunately the formula didn't work. Maybe i can explain the layout better. User ID is a driving examiner, Test ID is a test. So a driving examiner (User ID) is a unique value, Test (Test ID) is a unique value. However driving examiner (User ID) will conduct a number of tests daily. So what your seeing in screen shot 4 is a data dump, screen shot 3 is the same data but ive removed the duplicates so you can clearly see there are 6 of the same User ID'S due to that the driving examiner has conducted 6 tests (Test ID).

Hope this is a bit clearer, I've attached the file as well which will probably be helpful

:LDTM Time Study Analysis

Kind regards,
Thomas

 

[jasonb75]

I am trying to SUM a range(Duration) IF the Screen name is "Test report screen" and IF the test ID is unique and the user ID is unique.

Although you have asked for SUM, the way that you have phrased your question tells us that you only want the duration for an ID that only appears once in the list.

My ability to read minds has deteriorated of late, however, what I think that you actually want is to sum all instances of each unique combination, which would simply need

=SUMIFS(Table1[Duration],Table1[Screen Name],"Test report screen",Table1[Test ID],C2,Table1[User ID],B2)