+ Reply to Thread
Results 1 to 12 of 12

sum total of miles of vehicle by site

  1. #1
    Registered User
    Join Date
    04-22-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    39

    sum total of miles of vehicle by site

    Hi Guys,

    hopefully, this is a simple one but can't figure it and feeling useless, I need formulae that sum all miles drive for a site which has multiple vehicles

    so for example, each site has 4 vehicles and each vehicle lists its total distance lets say of 100 miles each the total for that site would be 400, I just need to get that total number

    I've removed most of the data but hopefully, the spreadsheet will make more sense
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: sum total of miles of vehicle by site

    In L3 copied down:

    =SUMIF($D$2:$D$15,LEFT(K3,1),$F$2:$F$15)
    Last edited by AliGW; 07-09-2020 at 09:49 AM. Reason: Typo corrected.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-22-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    39

    Re: sum total of miles of vehicle by site

    Thanks Ali, worked a treat, much appreciated

  4. #4
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: sum total of miles of vehicle by site

    Hi TaylorGC,

    Don't beat yourself up we're all still learning. Is this what you're after?

    In cell L3 =SUMIF($D$2:$D$15,LEFT(K3,1),$F$2:$F$15)

    Regards,

    Snook

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: sum total of miles of vehicle by site

    I see you have a column with Site, but that says a - new installs... while in column D you have just the site name. If the sites are single letters then we can use the left function to extact the a from a - new installs, but for now let me suggest that you put only the site name in column K so that we can used that for a simple sumif function:

    Put in L3, and drag down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: sum total of miles of vehicle by site

    Glad to help.

  7. #7
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: sum total of miles of vehicle by site

    Ali - There's a slight mistake in your solution, your 'sum_range' only goes to row 13 rather than 15.

    Snook

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: sum total of miles of vehicle by site

    Good spot!

  9. #9
    Registered User
    Join Date
    04-22-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    39

    Re: sum total of miles of vehicle by site

    Hi guys, so, that worked on the sample but when I put it in my full worksheet it doesn't work, is it something to do with the fact that I replaced the site names with letters (these would normally be places such as Glasgow, Warrington etc.)

    sorry I jumped the gun on saying it worked (which it did I guess )

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: sum total of miles of vehicle by site

    Hi TaylorGC,

    Did you see my post above? If you can't remove the "New Installs" part, then provide us with some sample data that is an example of what your real data looks like (doesn't have to be real, but illustrate what it looks like), a few lines should suffice.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: sum total of miles of vehicle by site

    If the lookup names exactly match the names in your table, just use this:

    SUMIF($D$2:$D$15,K3,$F$2:$F$15)

  12. #12
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: sum total of miles of vehicle by site

    No messy formula cluttering up your sheet.
    Macro on green button - add to your list without having to think about formula.
    torachan.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculating the total hours on site disregarding overlaps
    By msiobhan in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-12-2018, 02:07 AM
  2. Mileage Form...Need help with auto populating total miles
    By corinnag in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2017, 11:47 PM
  3. Index vehicle, driver and vehicle wise revenue report.
    By dackson in forum Excel Formulas & Functions
    Replies: 41
    Last Post: 06-02-2017, 01:40 AM
  4. [SOLVED] Sum quantity and show the total for each site in a table
    By shavar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2014, 10:43 AM
  5. Replies: 5
    Last Post: 08-30-2013, 04:32 PM
  6. calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate
    By infinite2006 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-30-2013, 08:26 AM
  7. Replies: 2
    Last Post: 01-30-2013, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1