Time Calculation

[Pippo83]

Hello,
I have a problem, I do not know how to get column J.
Help me, please.

[shivya]

=$j.

For more, attach sample sheet with desired result.

[Pippo83]

Sorry

This is file

[shivya]

Hello Pippo83,
Please mention,
How to calculate that value of column j?

[Pippo83]

Yes,I can not calculate column j

[shivya]

Yes,I can not calculate column j

Tell me, what is the procedure to get this result
I will tell you the formula only.

[shivya]

https://www.excelforum.com/excel-gen...tion-time.html

[Pippo83]

Tempo SLA =Tempo effettivo - transazioni escluse

J=I-H

[shivya]

=IFERROR(I2-H2,I2)

where is the issue?

[Pippo83]

Thanks.
But K6,K7,K9,K10,K11 is different J6,J7,J9,J10

[MrShorty]

Here's what I see:

1) column G is a simple difference between F and E.
2) column H is the difference between E and C when those conditions are met. Otherwise it is empty string "".
3) column I is a simple difference between C and B.
4) Column J looks like it takes the value in column I and subtracts the value of column H, if there is a value in H for the associated text string in column A.

Does that look correct? Which of those formulas would you need help with?

[Pippo83]

4) In the row I6 I get 0:31:22, instead I should get it 0:21:42 as J6

[MrShorty]

What formula are you using in I6? The current formula is simply $C6-$B6 with no reference to column H. I would have expected something like =$C6-$B6-$H$8, or something (a lookup function or sumifs() or something similar, depending on exactly what is supposed to happen) that dynamically picks up the value in H8.

[shivya]

Hello Pippo83,

Yes, I know that you want J6 as your result but you are not getting the same but pippo, We can't help you in this, If you will tell me the way to calculate then we can help in formula.

[Pippo83]

Thanks MrShorty, I do not know very well excell, so I asked for help to you. I do not know how to use the SUMIFS formula

[JeteMc]

I am making a guess that the logic here is that if any Tipo intervallo for a particular Numero TT has a Transazioni escluse value associated, that all of that particular Numero TT should subtract that Transazioni escluse value from the corresponding Tempo effettivo (F.I.).
If that is the case then select cell J2,
Paste the following array entered formula* into the formula bar:

Please Login or Register  to view this content.

*Simultaneously press the Ctrl, Shift and Enter keys,
With the cell still selected drag the fill handle down to cell J11.
Let us know if you have any questions.

[Pippo83]

Thanks a lot JeteMC.
I copied your formula, I got the column in yellow, but I should get the column in blue

[JeteMc]

The formula must be activated by pressing and holding the Ctrl and Shift keys while you press the Enter key before you copy it down to row 11.
Let us know if you have any questions.

[daddylonglegs]

Wouldn't SUMIF give the same results without array entry?

=I2-SUMIF(A$2:A$11,A2,H$2:H$11)

[Pippo83]

Great! It's fantastic !
Thanks so much daddylonglegss. Your formula is correct :-)