My weekly timesheets have daily mileage which I record & total but I also have to total any daily mileage over 100 miles in a day for the whole week is tried a few excel functions that don't give the desired result. Nearest I get is pick the days where mileage is above 100 but then adds those totals together instead of just adding the total number over 100 like I need. Any help much appreciated
Since you have not provided a sample workbook, so we are not aware of your sheet layout. But you can use SUMIF to total mileage which are greater than 100 like below.....
Assuming that your mileages are in col. B in the range of B2:B8 then
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
279.4 63.9 49.4 62.7 216.3 Day Off 671.7 Total Daily Miles Over 100 = 495.7
Thanks for that tried it but still adds up the 279.4 & 216.3 I know its a simple sum to just add the over 100 but I would like it to do it automatically. Never really used excel spreadsheets etc I'm just using it to do my daily & would like the weekly which all the rest is sorted apart from this one thing..
Please upload a sample workbook along with the expected output (filled manually). To upload a sample workbook, click Go Advanced and you will find a paper clip icon to upload a workbook.
Sorry I told you I was an amateur can't find go.. Oh & I am using office 2010 so is it the same principal for uploading a workbook.. Thanks...
Sorry got it
Very tame for you guys but needs must on a weekly basis.
Oh the desired filled in manually is the 295.7
Oh & just to state that is a for instance of this weeks travel. 2 days over 100 miles. I often do over 100 daily & would like it to update accordingly.. :-)
You have daily mileage in the range C12:G12 and these are 279.4, 63.9, 49.4, 62.7 and 216.3 respectively. If I understand it, you want to sum all the mileage which are greater than 100. Right?
So with the available daily mileage, there are two mileage 279.4 and 216.3 which are greater than 100 and if you sum these to mileages you get the total 495.7, right? So how did you get 295.7 in the cell L12, what is the logic behind it? I think you need to clarify it more.
It is required at the end of the week that we record any daily mileage over 100 so say I did 217 Monday & 163 Tuesday But nothing for the rest of the week the total miles over 100 for that week would be 180. Like I said simple sum but I would like it done automatically if possible.. Oh & no logic in it.. Its a work requirement.
OK. Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down Ctrl + Shift and then press Enter). When formula in the cell is an array formula, you will notice in the formula bar that the formula is surrounded by the curly braces.
Is this what you want?Please Login or Register to view this content.
Absolutely perfect Thank you for your help.. This is exactly what I was looking for.
Glad to help you. Thanks for the feedback.
If that takes care of your question, please mark your thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks