Struggling how to do a calculation

Copper Contributor

Hi, I am struggling on how to do a calculation, I have attached a spreadsheet below and would like to have a formula that takes the tick symbols of the contract type but only if there is no text in the left and reason column

11 Replies

@Tim_Jevans Try: =COUNTIFS(Clients!D4:D200,"ü",Clients!G4:G200,"")

Thank you this has helped perfectly, if possible could you help with my next problem i need to have the total hours gained and total hours lost actually be the hours and minutes instead of text 

but i can't seem to know how to do it @Savia 

I don't follow what you mean - can you explain a bit more?

So as you can see on the spreadsheet I have a column for hours and a column for minutes and in the calculation I have hours gained and hours lost however right now it is in text form which means when i put 40 hours and 15 minutes it comes up as 40.25 gained, however i want it in the format of 50 hours 15 minutes in the same cell, hopefully this was easier @Savia 

I understand now. Change the format to a custom number format of hh:mm.

I did that, however, it then says 06:00

Oh, I think you have a couple of problems. One is that you have a number of hours, whereas Excel counts hours as fractions. Add a /24 to your function to convert to hours. Secondly to display times over 24 hours without resetting then use [hh]:mm format.

I'm sorry to bother you but I don't understand it aha. are you able to help me solve this problem I need the minutes and hours adding up and being shown in an hour hour hour hour minute minute format

No problem. The formula you have right now returns a value of 40.25. Change the formula to add a divide by 24 to the end and then apply the custom number format [hh]:mm.

I have added it to the hours lost formula =SUM(SUMIF(Clients!G4:G200,"*",Clients!B4:B200)+CONVERT(SUMIF(Clients!G4:G200,"*",Clients!C4:C200),"mn","hr")/24) and it is returning 00:15 in the hh:mm format when the hours is 40 and the minutes is 15

The /24 is only dividing the minutes; it should be a division of the whole value. There's some excess stuff in there, so trimming that out I get:

=SUMIF(Clients!G4:G200,"*",Clients!B4:B200)/24+SUMIF(Clients!G4:G200,"*",Clients!C4:C200)/24/60

And the format should be [hh]:mm not hh:mm.