Home

Struggling how to do a calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-1192953%22%20slang%3D%22en-US%22%3EStruggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192953%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20struggling%20on%20how%20to%20do%20a%20calculation%2C%20I%20have%20attached%20a%20spreadsheet%20below%20and%20would%20like%20to%20have%20a%20formula%20that%20takes%20the%20tick%20symbols%20of%20the%20contract%20type%20but%20only%20if%20there%20is%20no%20text%20in%20the%20left%20and%20reason%20column%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1192953%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193003%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193003%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F567488%22%20target%3D%22_blank%22%3E%40Tim_Jevans%3C%2FA%3E%26nbsp%3BTry%3A%26nbsp%3B%3DCOUNTIFS(Clients!D4%3AD200%2C%22%C3%BC%22%2CClients!G4%3AG200%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193006%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193006%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20this%20has%20helped%20perfectly%2C%20if%20possible%20could%20you%20help%20with%20my%20next%20problem%20i%20need%20to%20have%20the%20total%20hours%20gained%20and%20total%20hours%20lost%20actually%20be%20the%20hours%20and%20minutes%20instead%20of%20text%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%26nbsp%3Bi%20can't%20seem%20to%20know%20how%20to%20do%20it%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193051%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193051%22%20slang%3D%22en-US%22%3EI%20don't%20follow%20what%20you%20mean%20-%20can%20you%20explain%20a%20bit%20more%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193058%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193058%22%20slang%3D%22en-US%22%3E%3CP%3ESo%26nbsp%3Bas%20you%20can%20see%20on%20the%20spreadsheet%20I%20have%20a%20column%20for%20hours%20and%20a%20column%20for%20minutes%20and%20in%20the%20calculation%20I%20have%20hours%20gained%20and%20hours%20lost%20however%20right%20now%20it%20is%20in%20text%20form%20which%20means%20when%20i%20put%2040%20hours%20and%2015%20minutes%20it%20comes%20up%20as%2040.25%20gained%2C%20however%20i%20want%20it%20in%20the%20format%20of%2050%20hours%2015%20minutes%20in%20the%20same%20cell%2C%20hopefully%20this%20was%20easier%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193195%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193195%22%20slang%3D%22en-US%22%3EI%20understand%20now.%20Change%20the%20format%20to%20a%20custom%20number%20format%20of%20hh%3Amm.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193237%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193237%22%20slang%3D%22en-US%22%3E%3CP%3EI%20did%20that%2C%20however%2C%20it%20then%20says%2006%3A00%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193275%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193275%22%20slang%3D%22en-US%22%3EOh%2C%20I%20think%20you%20have%20a%20couple%20of%20problems.%20One%20is%20that%20you%20have%20a%20number%20of%20hours%2C%20whereas%20Excel%20counts%20hours%20as%20fractions.%20Add%20a%20%2F24%20to%20your%20function%20to%20convert%20to%20hours.%20Secondly%20to%20display%20times%20over%2024%20hours%20without%20resetting%20then%20use%20%5Bhh%5D%3Amm%20format.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193299%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193299%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20sorry%20to%20bother%20you%20but%20I%20don't%20understand%20it%20aha.%20are%20you%20able%20to%20help%20me%20solve%20this%20problem%20I%20need%20the%20minutes%20and%20hours%20adding%20up%20and%20being%20shown%20in%20an%20hour%20hour%20hour%20hour%20minute%20minute%20format%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193313%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193313%22%20slang%3D%22en-US%22%3ENo%20problem.%20The%20formula%20you%20have%20right%20now%20returns%20a%20value%20of%2040.25.%20Change%20the%20formula%20to%20add%20a%20divide%20by%2024%20to%20the%20end%20and%20then%20apply%20the%20custom%20number%20format%20%5Bhh%5D%3Amm.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193330%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193330%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20added%20it%20to%20the%20hours%20lost%20formula%26nbsp%3B%3DSUM(SUMIF(Clients!G4%3AG200%2C%22*%22%2CClients!B4%3AB200)%2BCONVERT(SUMIF(Clients!G4%3AG200%2C%22*%22%2CClients!C4%3AC200)%2C%22mn%22%2C%22hr%22)%2F24)%20and%20it%20is%20returning%2000%3A15%20in%20the%20hh%3Amm%20format%20when%20the%20hours%20is%2040%20and%20the%20minutes%20is%2015%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193440%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20how%20to%20do%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193440%22%20slang%3D%22en-US%22%3EThe%20%2F24%20is%20only%20dividing%20the%20minutes%3B%20it%20should%20be%20a%20division%20of%20the%20whole%20value.%20There's%20some%20excess%20stuff%20in%20there%2C%20so%20trimming%20that%20out%20I%20get%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSUMIF(Clients!G4%3AG200%2C%22*%22%2CClients!B4%3AB200)%2F24%2BSUMIF(Clients!G4%3AG200%2C%22*%22%2CClients!C4%3AC200)%2F24%2F60%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20the%20format%20should%20be%20%5Bhh%5D%3Amm%20not%20hh%3Amm.%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

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

Highlighted

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 

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

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 

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

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

Highlighted
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.
Highlighted

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

Highlighted
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.
Highlighted

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

Highlighted
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.