+ Reply to Thread
Results 1 to 5 of 5

Counting cells based on date in column1 and an alpha-numeric value in column2

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    Loveland, CO
    MS-Off Ver
    2010 on Win 7 and 8.1
    Posts
    6

    Counting cells based on date in column1 and an alpha-numeric value in column2

    I don't know how to create a table, so I'll do my best to describe what I'm working with and what I would like to do.

    In summary, I'm trying to count the number of transactions a person performed by date. On Sheet1 Column C contains a list of User ID's (i.e. VL4321), and Column G contains a range of dates, with some dates repeating. So, my data looks like this:

    C1:C20 = VL4321, and G1:G5 = 5/10/2013, G6:G15 = 5/11/2013, G15:G20 = 5/12/2013
    C21:C30 = VL4081, and G21:G23 = 5/10/2013, G24:G28 = 5/11/2013, G29:G30 = 5/13/2013
    etc. etc.

    On Sheet2 I have a table in which I have User ID's in Column A, and a range of dates across Row1 )B1:N1.

    So, I need a way to determin how many times the User ID listed in cell A2 appears with a corresponding date in cell B1 in the original data set on Sheet1

    I've tried a number of COUNTIF, COUNTIFS, and SUMPRODUCT variations, but have only come up with a bunch of #VALUE and #N/A errors that I cannot seem to resolve.

    Any help would be greatly appreciated!
    Last edited by JStreck; 05-24-2013 at 12:46 PM. Reason: Solved

  2. #2
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: Counting cells based on date in column1 and an alpha-numeric value in column2

    Have you tried putting this formula in cell B2 and dragging it across?

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


    That should give you what you're looking for. If that isn't working, check the formatting in your cells.

    For future reference, It's easier if you upload a spreadsheet of what your trying to do with all the sensitive data scrubbed out of it.

  3. #3
    Registered User
    Join Date
    05-24-2013
    Location
    Loveland, CO
    MS-Off Ver
    2010 on Win 7 and 8.1
    Posts
    6

    Re: Counting cells based on date in column1 and an alpha-numeric value in column2

    That worked perfectly! Thank you for the quick response, and for the simple solution.

    I agree uploading a spreadsheet would have been easier, but I couldn't figure out how to do so. I'll work on that for future reference as you suggest.

  4. #4
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: Counting cells based on date in column1 and an alpha-numeric value in column2

    You're welcome. You should mark the thread as solved if you're happy with the answer.

    To mark the thread as solved, edit the first post --> click on advanced --> update thread title prefix.

  5. #5
    Registered User
    Join Date
    05-24-2013
    Location
    Loveland, CO
    MS-Off Ver
    2010 on Win 7 and 8.1
    Posts
    6

    Re: Counting cells based on date in column1 and an alpha-numeric value in column2

    Done - thanks again, I was trying to figure out how to do that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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