How to count lines in excel without selecting them

[chubbybat]

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)

 

[T. Valko]

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))))

 

[chubbybat]

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









- 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")*(D27000="8:30:00"))