DCOUNT, VLOOKUP not working for my project

[Kerichka]

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)

[JBeaucaire]

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.

[Kerichka]

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]

[JBeaucaire]

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.

[Kerichka]

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.

[Kerichka]

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

[JBeaucaire]

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.

[Kerichka]

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.