Thread: Arrays based off a separate tab

Hello all!

I have never really worked with Excel arrays but am not unfamiliar with how an array works and think that it is what I need to use to solve my problem.

I have a daily report in Excel that is based on daily registrations that I pull off of a web based report. I pull the report each day and save an Excel version of it and then populate the numbers in the daily report based on filtering by client, location, registration type, etc. Needless to say its a manual pain in the you-know-what =)

I thought about creating a solution where I take the registration report and put it in a separate tab in the daily report and then for each cell I calculate the number of registrations based on the criteria. So for example, let's say the registration report looks like this:


Date Registered Location Session Registration Type Name
10/1/2011 Tinley Park 1 Phone Bob Jones
10/1/2011 Oak Forest 3 Online John Smith
10/3/2011 Oak Forest 3 Online Betty White
10/4/2011 Tinley Park 2 Phone Mary Green
10/5/2011 Tinley Park 1 Online Joe Walker
10/5/2011 Tinley Park 2 Online Kate Davis
10/5/2011 Oak Forest 3 Online Alex Trebeck

And I want to populate a cell with the number of Online registrations on 10/5/2011. I started by looking at a countif statement in conjunction with an array, but it doesn't seem to be producing the results I'm looking for.

Also, I want to match the column header (which is today's date) with the number of records that match that date.

Any ideas? Or ways that this could be more easily done? Any help is appreciated. Thanks in advance!

If I were setting up the report from scratch, I'd be all for it. The other thing about this report is that Oak Forest results would need to be in a separate tab from Tinley Park results, which I suppose could be accomplished in multiple pivot tables. My goal is to just overwrite one tab with the results daily and have the remaining tabs populate with the results automatically.

I figured out how to do it with a COUNTIFS, which will do the trick for now. Thanks for your help!