Good morning to all of you.

Has anyone faced the following situation?

I have a list that contains the following columns
- Day of the month
- Time

I would like to create a summary table using the SUMPRODUCT formula to
determine the number of calls per time period during the day in a dynamic
way.
That would mean:
- having a formula or macro that would scan the Day column and list unique
occurences of a day (for instance Day2, day1, day1, day2, day2, day2, day2,
day3... would give me a list with 3 values Day 1, Day 2, day 3
- having a formula or macro that would scan the timecolumn and list unique
occurences (7-8, 7-8, 8-9, 8-9...) would give me a list with 2 values 7-8,
8-9

Final result like
7-8 8-9
day 1 1 1

day 2 1 1

Thanks for your help in this matter

I'm not sure if you are limited to just a macro but a pivot table would do
the job.

Make the day the first row field. Make the time the 2nd row field (or a
column field, column field may be better visually). Make either the day or
the time a value field as well and change it to count (# of occurences).

This will produce unique day / time combinations and also give you a count
of the number of occurences of each.

Hope that helps.

Bill Horton

"MIchel Khennafi" wrote:

Good morning to all of you.

Has anyone faced the following situation?

I have a list that contains the following columns
- Day of the month
- Time

I would like to create a summary table using the SUMPRODUCT formula to
determine the number of calls per time period during the day in a dynamic
way.
That would mean:
- having a formula or macro that would scan the Day column and list unique
occurences of a day (for instance Day2, day1, day1, day2, day2, day2, day2,
day3... would give me a list with 3 values Day 1, Day 2, day 3
- having a formula or macro that would scan the timecolumn and list unique
occurences (7-8, 7-8, 8-9, 8-9...) would give me a list with 2 values 7-8,
8-9

Final result like
7-8 8-9
day 1 1 1

day 2 1 1

Thanks for your help in this matter

If you use a pivot table, you may want to first convert the time to an
hour of the day, assuming you are looking for the number of occurrences
in each hour of the day, rather than the number of occurrences of each
specific time. You can do this with the formula =HOUR(time), where
"time" refers to a cell in the time column.

If you don't want to use a pivot table, you should still add a column
to identify the thime interval (hour) within which the time falls and
then set up a matrix, as you seem to have planned to do, with the dates
on the left and the intervals across the top.

Assume your list of dates are in A2:A16 and time intervals are in
C2:C16.
Assume your matrix dates are listed downwards from E2 and your matrix
time intervals are listed across starting in F1.

Enter in F2,

=SUMPRODUCT(--($A$2:$A$16=$E2),--($C$2:$C$16=F$1))

and drag/copy across and down as far as necessary to cover all dates
and time intervals.


HTH

DOR