+ Reply to Thread
Results 1 to 8 of 8

DCOUNT, VLOOKUP not working for my project

  1. #1
    Registered User
    Join Date
    06-01-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    DCOUNT, VLOOKUP not working for my project

    Hi, all,

    I have a list of about 300 unique names on sheet1 and a spreadsheet with about 17000 rows, representing one week of account activity, on sheet2. These rows contain multiple occurrences of each name, along with dates, times and minutes of use. On sheet3 I have broken down the range of dates and time frames I am trying to isolate into a number of criteria tables.

    I need to determine the activity for each user within a designated time frame. I need a both a count of occurences and a sum of the minutes.

    I've been trawling the net all morning and struggling away with DCOUNT, IF, VLOOKUP and a pivot tables, but so far have failed to find a solution. DCOUNT leaves the names out, VLOOKUP and pivot tables leave the time frame out, and IF doesn't seem to work at all.

    Any suggestions?

    Edited to add, this is the type of thing I am trying to do, which does not work:
    =IF('sheet2'!A:A='sheet1'B5, DCOUNT('sheet2'!,12,'sheet3'!$B$3:$E$7), 0)
    Last edited by Kerichka; 06-01-2009 at 06:41 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: DCOUNT, VLOOKUP not working for my project

    Hi Kerichka, welcome to the forum.

    This sounds pretty straightforward. Help us help you more quickly by giving us a good sampling of your data.

    Click on GO ADVANCED and use the paperclip to upload your sample sheet. Be sure the sample has enough data in it for some real calculation testing.

    Also, make sure you've laid out where you're wanting to do this "reporting"...show us your goal line. Is it a report of everyone or just one select name and date range? Show us and we can help more quickly.

  3. #3
    Registered User
    Join Date
    06-01-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: DCOUNT, VLOOKUP not working for my project

    Hi, JBeaucaire,

    I tried to mock up a depersonalized example, but the upload failed so I assume I can't do this at work. Sorry!

    Here's a text version - hopefully you can put it into notepad and open it in excel.

    [snip]
    Last edited by Kerichka; 06-01-2009 at 04:20 PM.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: DCOUNT, VLOOKUP not working for my project

    No, I don't think so.

    Perhaps you have some other place you can upload to and provide a link. Else, keep trying to post a small sample file here. Perhaps it takes a few posts to be able to do so from where you are.

    Also, on the uploaded version, be sure to point out exactly where you're wanting help, perhaps fill in some sample "answers" to make it clearest.

  5. #5
    Registered User
    Join Date
    06-01-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: DCOUNT, VLOOKUP not working for my project

    Quote Originally Posted by JBeaucaire View Post
    No, I don't think so.

    Perhaps you have some other place you can upload to and provide a link. Else, keep trying to post a small sample file here. Perhaps it takes a few posts to be able to do so from where you are.

    Also, on the uploaded version, be sure to point out exactly where you're wanting help, perhaps fill in some sample "answers" to make it clearest.
    Hi, I am assuming that if the security settings here don't allow uploading, there won't be another place online I can upload and link to either.

    Oh well, thanks anyway. I will keep trawling the net. If I can't find a solution by the end of today I suppose I can spend the next few days typing it all in manually. I'll bring in an audiobook (or three) on my MP3 player to alleviate the boredom.

  6. #6
    Registered User
    Join Date
    06-01-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: DCOUNT, VLOOKUP not working for my project

    Aha - I think it was a file size issue, not a security issue. Let me try again...

    Looks like it uploaded zipped. If I don't reply, just assume I've been sacked for this.

    Thanks again,
    K
    Attached Files Attached Files

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: DCOUNT, VLOOKUP not working for my project

    Well, three tiny sample sheets should NOT be a 3 megabyte file, eh? Something is awry on your sheet/computer. I put those three sets of data into a new sheet and the sheet dropped to nothing in size.

    I'm not sure is a fancy array is in order here or not. I added a simple RATE column to the data itself so each line can identify its own rate code, then used those codes to do a SUM in the categories you asked for.

    On the report page you can see the "table" I created that the Rate column is using to look up its rates.

    Anyway, put in the Sunday date in O2 and the report will update itself.

    NOTE, the formulas in each set are the same except for section 3, which simply adds the values in sections and 2 & 4 and subtracts section 5. No need to add all that again when the numbers are right there.

    Have a look.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-01-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: DCOUNT, VLOOKUP not working for my project

    That's smashing. Thanks! Don't know what the deal is with the file size. I ended up doing something similar (adding a column to the source data) and doing pivot tables based on that, but your solution is way better.

+ 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