Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to count maximum number of lines repeated for a site | Excel Worksheet Functions | |||
Inserting Lines or Copying lines with formulas but without data | Excel Discussion (Misc queries) | |||
Count the number of Lines in a "wrap text"-cell | Excel Discussion (Misc queries) | |||
lines colors when selecting rows | Setting up and Configuration of Excel | |||
how to display the count of lines found by autofilter? | Excel Discussion (Misc queries) |