SUM(if) function for multiple criteria + date/time that is dynamic

Question

I got a problem to "extend" my formula to make correct calculation, in my case SUM() of !Time difference in Column G OR !% of time in Column H using criteria in !System in Column K in certain !date Column C. So, as you can notice there are couple of events per one day and with its given !time Column E (for instance 2019-01-10 has 5 events, but the number isnt always the same).

What I actually wanted is to SUM() partially how many (total time per day) of ST and SC (from System Column) are contained per one day, and how to solve that with changeable Date/Time? I mean, manually will take me forever, because I have like over 1000 such a events, and I wanted to make it per day if its possible, or do you guys got any other suggest??

The goal is to calculate utilization rate per one day for both System types.

I used simple =SUMIF as you can see on L2, but I am pretty sure it has to be combined with way more functions and criterias..

I would appreciate your help.

Answer 1

My gratitude to all Users that helped me with an idea, what, where and how to look for my case. So I can post (for my purposes of course) some Infos for other that will be asking, eventually.

Function used: =SUMIFS($H:$H;$L:$L;M$1;D:D;$D2) for both Systems in my case. And as you can notice in Columns M and N there are repetitive numbers (percentages). Usually, if you show that on charts it will give you total SUM or Average what actually is not what I asked. I needed SUM per day per System.

This was easy task, just remove duplicates and you are done.

Also, you can do using Pivot tables, but it is important to have "input" data good organised.

I dont need to explain Pivot I guess =)

The only calculation I did here is % of time using MOD function: MOD(F3-F2;1) where I calculated Time difference between 2nd date and 1st date. Then that "time" just converted to percentage. And good thing in Pivot, it recognizes it automatically as SUM, and you got exactly the same stuff as I wrote in first part using just SUMIFS and remove duplicates.

*Time difference is between those repetitive dates (2019-01-10) stated in first Picture in column D.

I am pretty sure there are many other ways, but I am happy that I got it this way(s), and always learned something new.

Thank you for your help!

Greets

Answer 2

Please refer the below snap to simplify your requirement.

please let me know if need to any more method.

If this not suits for your requirement you can create pivot table, which can be easier to get your result. The same have been explained by others in comment.

Formula For First Method: =SUMIFS($E:$E,$H:$H,L$10,$A:$A,$K11)

Formula For Second Method: =SUMIFS($E:$E,$H:$H,K$21,$A:$A,$L19)