+ Reply to Thread
Results 1 to 6 of 6

add mileages together for the state

  1. #1
    Registered User
    Join Date
    01-05-2016
    Location
    Richmond, VA
    MS-Off Ver
    Office 2013
    Posts
    22

    Question add mileages together for the state

    Trying to come up with a way to add a series of numbers together. I have Column A with a state in it, Column B has starting mileage in that state, Column C has ending mileage in that state, Column D is the difference (or total miles driven within the state). Need to figure out how to add all of the different state's miles driven together without clicking each state. I have a column of 679 values that I need to apply this too. Here is a small chunk for visual...Can you Help me?
    State Start Mile End Mile Total Miles
    MD 109680 109725 45
    VA 109725 109830 105
    VA 109830 109935 105
    MD 109935 110050 115
    MD 110050 110055 5 MD Total miles:
    DE 110055 110070 15 NJ total miles:
    NJ 110070 110220 150 VA Total Miles:
    NY 110220 110355 135 DE total Miles:
    NJ 110355 110505 150 NY total Miles:

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,411

    Re: add mileages together for the state

    Probably simplest to use a Pivot Table and sum the values in the difference column.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: add mileages together for the state

    Like this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    State
    Start Mile
    End Mile
    Total Miles
    ------
    State
    Total Miles
    2
    MD
    109680
    109725
    45
    MD
    165
    3
    VA
    109725
    109830
    105
    NJ
    300
    4
    VA
    109830
    109935
    105
    VA
    210
    5
    MD
    109935
    110050
    115
    DE
    15
    6
    MD
    110050
    110055
    5
    NY
    135
    7
    DE
    110055
    110070
    15
    8
    NJ
    110070
    110220
    150
    9
    NY
    110220
    110355
    135
    10
    NJ
    110355
    110505
    150


    This formula entered in G2 and copied down:

    =SUMIF(A:A,F2,D:D)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    01-05-2016
    Location
    Richmond, VA
    MS-Off Ver
    Office 2013
    Posts
    22

    Re: add mileages together for the state

    Thank you so very much it works!!!

  5. #5
    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
    80,626

    Re: add mileages together for the state

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: add mileages together for the state

    You're welcome. Thanks for the feedback!

+ 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] state abbreviations to regular state names
    By ammartino44 in forum Excel General
    Replies: 2
    Last Post: 05-19-2015, 01:39 PM
  2. Mileages
    By drums4monty in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-20-2015, 12:19 PM
  3. 50 States, Drop-Down List, select a state then produce the # for that state
    By Sir CHARLES in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-30-2015, 07:46 PM
  4. [SOLVED] Lookup State Name from State Code and and Display in column in COUNTIES worksheet.
    By Jim15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2014, 02:41 PM
  5. calculating mileages for several vehicles
    By voodoo_ca in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-21-2014, 05:56 PM
  6. Excel 2007 : Mileages
    By UKAndy in forum Excel General
    Replies: 5
    Last Post: 07-03-2010, 04:59 PM
  7. Working with mileages
    By kiakanpa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2007, 05:46 AM

Tags for this Thread

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