+ Reply to Thread
Results 1 to 6 of 6

INDEX and MATCH functions to sum data with blank cells

  1. #1
    Registered User
    Join Date
    05-10-2013
    Location
    Chapel Hill, NC
    MS-Off Ver
    Excel 2010
    Posts
    3

    INDEX and MATCH functions to sum data with blank cells

    Hi everyone,

    I am looking for a formula that will work with the attached data set. What I want to be able to do is create a formula that is fully automated -- I need a formula that would basically say this for example: IF "6 Carter Finley" (column A), AND "Carter-Finley Park & Ride" (column B) THEN add all on-time departures (column S) that meet that criteria. So the end result would be a formula that would tell me the sum of all on-time departures, at the Carter-Finley Park & Ride, for the 6 Carter Finley.

    I think the trouble I am having is two things -- the routes repeat through the table and there are spaces. I would also like to build the formula so I wont have to manipulate the raw data at all -- basically just copy and paste it in -- so the list would be a placeholder like "A1:A10000" (because the number of rows may vary from time to time). The route ordering changes from time to time, so for example "6 Carter Finley" wont always be in A51 each month.

    I've searched the boards and founds tips on using INDEX and MATCH (including using ISBLANK), SUMIFS, SUMPRODUCT, etc. I've also done the cntl-shift-enter function to make the formula an array function but it is still giving me errors.

    Please see the attachment and let me know if you all devise any formulas that work with my data. Thank you!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: INDEX and MATCH functions to sum data with blank cells

    What's the reasons of hiding some rows?

    Those data may not incluced your sum?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: INDEX and MATCH functions to sum data with blank cells

    With the macro Sub filldata_acending_incolumnA()

    And after that an pivot table.

    See the attached file.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-10-2013
    Location
    Chapel Hill, NC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: INDEX and MATCH functions to sum data with blank cells

    Hi Oeldere,

    Some of the rows/columns are hidden because they are just extra detail -- no impact on the total calculation. I was trying to avoid having to create a pivot table with the data -- I was hoping to try and find a formula that could do it all at once. If I can't find an all-in-one formula, this is definitely a good back-up!

    Anyone else know of a method that would be an all-in-one formula to solve this?

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: INDEX and MATCH functions to sum data with blank cells

    I was trying to avoid having to create a pivot table with the data

    Such an powerfull tool and you want to avoid it.

    Just wondering why?

  6. #6
    Registered User
    Join Date
    05-10-2013
    Location
    Chapel Hill, NC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: INDEX and MATCH functions to sum data with blank cells

    Since I need to update this data calculation frequently, I like the automation of a single formula. Creating a pivot table adds one extra step that a single formula would not, If I could figure out one that will work with this data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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