Excel 2010 - Arrays 3

[laina222]

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!

 

[SkipVought]

Using your headings as the basis for Named Ranges,
[tt]
I2: =COUNTIFS(Type,$H2,Date_Registered,">="&I$1,Date_Registered,"<"&J$1)
[/tt]
where H2 has Phone of Onling and I1 has 10/1/2011 and J2 has 11/1/2011

I get 2 phone and 5 Online using your data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[laina222]

Thanks! That worked quite well!