How to count lines in excel without selecting them
 

Hi all,

I was hoping someone could help with me with a formula.
I have a worksheet with 6000 lines exported from a room booking
program called Syllabus Plus.
Result required- count the number of bookings made each hour from 8am
-6pm Monday to Friday.
So on each line all I have is the time of the booking and the day
(and
other data which I remove).
Presently I sort all the data into day and time of booking then do a
sum for each group of bookings.


What I would like is a formula that adds all the lines that say
Monday
8:00, Monday 9:00, Tuesday 4:00 etc then I can turn these results
into
a graph.
Any Uber Geek that can help would be much appreciated.


Kindly
Dave (Chubbybat)

How to count lines in excel without selecting them
 

Assuming:

Column A are times
Column B are weekday names as TEXT entries in long name format (Friday, not
Fri)

=SUMPRODUCT(--(A1:A20=TIME(8,0,0)),--(A1:A20<=TIME(16,0,0)),--(ISNA(MATCH(B1:B20,{"Saturday";"Sunday"},0))))


--
Biff
Microsoft Excel MVP


"chubbybat" wrote in message
...
Hi all,

I was hoping someone could help with me with a formula.
I have a worksheet with 6000 lines exported from a room booking
program called Syllabus Plus.
Result required- count the number of bookings made each hour from 8am
-6pm Monday to Friday.
So on each line all I have is the time of the booking and the day
(and
other data which I remove).
Presently I sort all the data into day and time of booking then do a
sum for each group of bookings.


What I would like is a formula that adds all the lines that say
Monday
8:00, Monday 9:00, Tuesday 4:00 etc then I can turn these results
into
a graph.
Any Uber Geek that can help would be much appreciated.


Kindly
Dave (Chubbybat)

How to count lines in excel without selecting them
 

On Mar 5, 2:17*pm, "T. Valko" wrote:
Assuming:

Column A are times
Column B are weekday names as TEXT entries in long name format (Friday, not
Fri)

=SUMPRODUCT(--(A1:A20=TIME(8,0,0)),--(A1:A20<=TIME(16,0,0)),--(ISNA(MATCH(*B1:B20,{"Saturday";"Sunday"},0))))

--
Biff
Microsoft Excel MVP

"chubbybat" wrote in message

...



Hi all,

I was hoping someone could help with me with a formula.
I have a worksheet with 6000 lines exported from a room booking
program called Syllabus Plus.
Result required- count the number of bookings made each hour from 8am
-6pm Monday to Friday.
So on each line all I have is the time of the booking and the day
(and
other data which I remove).
Presently I sort all the data into day and time of booking then do a
sum for each group of bookings.

What I would like is a formula that adds all the lines that say
Monday
8:00, Monday 9:00, Tuesday 4:00 etc then I can turn these results
into
a graph.
Any Uber Geek that can help would be much appreciated.

Kindly
Dave (Chubbybat)- Hide quoted text -

- Show quoted text -

HI there,

thanks for your time however that is a little off track due to my bad
explaining.
PPlease see new post called
=SUM((C2:C7000="Monday")*(D2:D7000="8:30:00"))