+ Reply to Thread
Results 1 to 6 of 6

Match values in multiple cells to a list/range and count/sum how many times they appear

  1. #1
    Registered User
    Join Date
    07-22-2014
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    3

    Match values in multiple cells to a list/range and count/sum how many times they appear

    Excel Screen Capture.JPG

    Please can someone help me. I have tried looking this up using Excel Help websites, but can only find a formula that compares a value in one cell with values from a list/range, but I have values in multiple cells that I want to cross reference/compare against a list/range.

    I want to be able to count how many pilots have worked on any given day.

    The names of the pilots who have 'actually' worked are recorded in columns B, D, F and H (please see attached example screenshot)

    A list of all Pilot names are recorded on another sheet e.g. Crew!A2:A11

    On a typical day, 4 pilots are rostered and 4 pilots actually work, but on some days, it is necessary for a pilot to do a double shift resulting in only 3 individuals working that particular day.

    I would like a formula that cross references the names of pilots who actually worked (on any given day) with the list of all pilot names and if there is a match, return a value of 1 and then sum up how many matches there are e.g. if a pilot takes another pilots place (double shift), there will only be 3 names (not 4) for that day.

    I Hope this makes sense.

    Thank you.

    Gaynor

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Match values in multiple cells to a list/range and count/sum how many times they appea

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Match values in multiple cells to a list/range and count/sum how many times they appea

    Hi,

    Welcome to the Forum.

    Please upload the sample template in excel format using "Go Advanced" option.

  4. #4
    Registered User
    Join Date
    07-22-2014
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    3

    Re: Match values in multiple cells to a list/range and count/sum how many times they appea

    Can someone advise if this has worked?
    Attached Files Attached Files
    Last edited by GMorris; 07-22-2014 at 02:29 AM. Reason: Wrong version attached

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Match values in multiple cells to a list/range and count/sum how many times they appea

    Maybe this?

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

  6. #6
    Registered User
    Join Date
    07-22-2014
    Location
    Brisbane
    MS-Off Ver
    2010
    Posts
    3

    Re: Match values in multiple cells to a list/range and count/sum how many times they appea

    Thanks for this :-)

    No, it doesn't calculate right. Could it be because it includes the pilots who were "planned", rather than just those that were actually on duty e.g. columns E, G, I and K.

    Sorry, as you can probably tell, I'm not experienced enough in Excel to explain properly what I want.

    I guess a simpler way to do it, would be to count unique names that appear in columns E, G, I and K, and this would tell you how many individuals were on duty for that day. The only problem is that some pilots have the same Christian name and I would like the option to keep adding to the list of names of pilots in a separate spreadsheet, rather than having to play around with the formula.

    Thanks again for all your assistance with this.

    G

+ 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] In a range of days, count how many times one value falls between two other values
    By Araise in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2014, 08:10 PM
  2. Replies: 8
    Last Post: 07-14-2013, 07:29 PM
  3. Filter A Pivot Table Multiple Times Based On Values In List In Another Sheet
    By w.m.christensen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-24-2013, 03:55 AM
  4. copying 2 cells multiple times to a range
    By NewToVBA_23 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2013, 06:09 PM
  5. Replies: 2
    Last Post: 11-16-2007, 10:36 AM

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