Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
countif / sumproduct or something else?
Hi, I have a table detailing staff duties in 10 minute blocks. I use one sheet for each day of the week, and have included a summary worksheet which uses a countif function to total the amount of duties performing a certain task in a given 10 minute block. this (extract) is the 10 minute data for Monday. No. 06:00 06:10 06:20 06:30 06:40 06:50 1 PA BM BM BM BM BM 2 BM BM BM BM BM BM 3 MHE MHE MHE MHE MHE MHE 4 MHE MHE MHE MHE MHE MHE 5 PM PM PM PM PM PM 6 PM PM PM PM PM PM 7 MHE MHE MHE MHE MHE MHE 8 MHE MHE MHE MHE MHE MHE This (extract) is the summary count for MONDAY 06:00 06:10 06:20 06:30 PA 1 0 0 0 PM 42 42 42 42 MHE 10 10 10 10 XD 0 0 0 0 BM 1 2 2 2 GH 2 2 2 2 AT 1 1 1 1 My question is - I want to reduce the seven daily worksheets to one sheet and add seven columns to determine if a duty occurs on a given day. See below. How would I then produce a summary worksheet to total the tasks in 10 minute blocks providing the task occurs on a Monday, Tuesday etc? I thought of sumproduct or a countif with two arguments? It works so well with seven seperate worksheets, but for maintainence it would be better to use one worksheet. I think a macro which loops through every cell in the range (30,000 plus cells), would take too long as it would be updated regularly. Any thoughts? Duty M Tu W 06:00 06:10 1 Y Y Y PA BM 2 Y Y BM BM 3 Y Y Y MHE MHE 4 Y Y Y MHE MHE 5 Y Y Y PM PM 6 Y PM PM 7 Y Y Y MHE MHE 8 Y Y Y MHE MHE |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
countif / sumproduct or something else?
Worksheet functions are less efficient and run slower than VBA code.
Worksheet functions will slow down the worksheet because it has recaculate after every entry. I put numbers in the worksheet 100 columns wide and 300 rows long to get 30,000 data points. I ran the code below which took about 1 to 2 seconds to run Sub count() Dim Total As Long StartTime = Now() Total = 0 For RowCount = 1 To 300 For ColCount = 1 To 100 Total = Total + Cells(RowCount, ColCount) Next ColCount Next RowCount EndTime = Now() MsgBox (Format(EndTime - StartTime, "SS")) End Sub "MJKelly" wrote: Hi, I have a table detailing staff duties in 10 minute blocks. I use one sheet for each day of the week, and have included a summary worksheet which uses a countif function to total the amount of duties performing a certain task in a given 10 minute block. this (extract) is the 10 minute data for Monday. No. 06:00 06:10 06:20 06:30 06:40 06:50 1 PA BM BM BM BM BM 2 BM BM BM BM BM BM 3 MHE MHE MHE MHE MHE MHE 4 MHE MHE MHE MHE MHE MHE 5 PM PM PM PM PM PM 6 PM PM PM PM PM PM 7 MHE MHE MHE MHE MHE MHE 8 MHE MHE MHE MHE MHE MHE This (extract) is the summary count for MONDAY 06:00 06:10 06:20 06:30 PA 1 0 0 0 PM 42 42 42 42 MHE 10 10 10 10 XD 0 0 0 0 BM 1 2 2 2 GH 2 2 2 2 AT 1 1 1 1 My question is - I want to reduce the seven daily worksheets to one sheet and add seven columns to determine if a duty occurs on a given day. See below. How would I then produce a summary worksheet to total the tasks in 10 minute blocks providing the task occurs on a Monday, Tuesday etc? I thought of sumproduct or a countif with two arguments? It works so well with seven seperate worksheets, but for maintainence it would be better to use one worksheet. I think a macro which loops through every cell in the range (30,000 plus cells), would take too long as it would be updated regularly. Any thoughts? Duty M Tu W 06:00 06:10 1 Y Y Y PA BM 2 Y Y BM BM 3 Y Y Y MHE MHE 4 Y Y Y MHE MHE 5 Y Y Y PM PM 6 Y PM PM 7 Y Y Y MHE MHE 8 Y Y Y MHE MHE |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
countif / sumproduct or something else?
You are correct in principle Sandy, that is an amazing statement by Joel,
but often VBA can be quicker than a whole raft of worksheet functions, if intelligently designed. The VBA will not be quicker than a single worksheet function, but by doing less the net effect can be beneficial. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Worksheet functions are less efficient and run slower than VBA code. Worksheet functions will slow down the worksheet because it has recaculate after every entry. Not true. Worksheet functions are MUCH faster than VBA and will only calculate the other cells that are dependant on the changed cell unless there are more than 65,536 unique dependant references. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Joel" wrote in message ... Worksheet functions are less efficient and run slower than VBA code. Worksheet functions will slow down the worksheet because it has recaculate after every entry. I put numbers in the worksheet 100 columns wide and 300 rows long to get 30,000 data points. I ran the code below which took about 1 to 2 seconds to run Sub count() Dim Total As Long StartTime = Now() Total = 0 For RowCount = 1 To 300 For ColCount = 1 To 100 Total = Total + Cells(RowCount, ColCount) Next ColCount Next RowCount EndTime = Now() MsgBox (Format(EndTime - StartTime, "SS")) End Sub "MJKelly" wrote: Hi, I have a table detailing staff duties in 10 minute blocks. I use one sheet for each day of the week, and have included a summary worksheet which uses a countif function to total the amount of duties performing a certain task in a given 10 minute block. this (extract) is the 10 minute data for Monday. No. 06:00 06:10 06:20 06:30 06:40 06:50 1 PA BM BM BM BM BM 2 BM BM BM BM BM BM 3 MHE MHE MHE MHE MHE MHE 4 MHE MHE MHE MHE MHE MHE 5 PM PM PM PM PM PM 6 PM PM PM PM PM PM 7 MHE MHE MHE MHE MHE MHE 8 MHE MHE MHE MHE MHE MHE This (extract) is the summary count for MONDAY 06:00 06:10 06:20 06:30 PA 1 0 0 0 PM 42 42 42 42 MHE 10 10 10 10 XD 0 0 0 0 BM 1 2 2 2 GH 2 2 2 2 AT 1 1 1 1 My question is - I want to reduce the seven daily worksheets to one sheet and add seven columns to determine if a duty occurs on a given day. See below. How would I then produce a summary worksheet to total the tasks in 10 minute blocks providing the task occurs on a Monday, Tuesday etc? I thought of sumproduct or a countif with two arguments? It works so well with seven seperate worksheets, but for maintainence it would be better to use one worksheet. I think a macro which loops through every cell in the range (30,000 plus cells), would take too long as it would be updated regularly. Any thoughts? Duty M Tu W 06:00 06:10 1 Y Y Y PA BM 2 Y Y BM BM 3 Y Y Y MHE MHE 4 Y Y Y MHE MHE 5 Y Y Y PM PM 6 Y PM PM 7 Y Y Y MHE MHE 8 Y Y Y MHE MHE |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
countif / sumproduct or something else?
Thanks, for the discussion, very interesting. I'm not sure that the
code above solves my problem though. How can I count each occurance of task A in the timeslot 06:00 to 06:10 for 200 rows of data, only if the duty occurs on a Monday and then place the result in a summary sheet, then move onto the next time slot, and after all timeslots have been queried, query the same data for Task B, after that, I need to do it all again but ask if Tuesday is a day on which the duty occurs etc etc. Each row represents a duty and its content (tasks split to minute blocks of time), the row also details the days on which the duty occurs. Like I say a COUNTIF works, but when I used this I have seven worksheets (one for each day of the week), now I only have the one worksheet and state for each duty the days the duty occurs. fingers are crossed, kind regards, Matt |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
countif / sumproduct or something else?
Hi Matt,
Speaking for myself, I do not understand your sample data: In the first exteact I assume that the numbers are table numbers? In the second extract I dn't understand why PM has 42 and MHE has 10. What do these numbers represent? In the third extract you have M Tu W and times. Do you want to know only if a person works that day or the time that they worked? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "MJKelly" wrote in message ... Thanks, for the discussion, very interesting. I'm not sure that the code above solves my problem though. How can I count each occurance of task A in the timeslot 06:00 to 06:10 for 200 rows of data, only if the duty occurs on a Monday and then place the result in a summary sheet, then move onto the next time slot, and after all timeslots have been queried, query the same data for Task B, after that, I need to do it all again but ask if Tuesday is a day on which the duty occurs etc etc. Each row represents a duty and its content (tasks split to minute blocks of time), the row also details the days on which the duty occurs. Like I say a COUNTIF works, but when I used this I have seven worksheets (one for each day of the week), now I only have the one worksheet and state for each duty the days the duty occurs. fingers are crossed, kind regards, Matt |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
countif / sumproduct or something else?
Hi Sandy,
I have 200 duties, one on each row. Each duty is broken down into ten minutes time slots so I know the task they are performing in each ten minutes (as the tasks change during the duty). Their are 30 different tasks which can be performed. So, originally each day of the week had a worksheet and each duty which occurred on that day was included. A simple Countif was used to count all instances of each task in the first time slot for the 200 duties, then the next summary cell counted the second time slot (again instances of the different tasks). I used a summary table for each day of the week. But, now I want to use one worksheet to contain the duties. Each row still represents one duty, but if the duty occurs on Monday or Tuesday etc then a "Y" is entered against that duty for the corresponding day column. How would I now produce a summary which checks to see if the duty occurs on a given day, and how many times each task occurs in the time slot for the total 200 duties. Again I would have a summary table for each day and a total table to show total amount for each task for the week (again in time slots). Like I say it works if the duties are input into different worksheets (one for each day), but for maintaining the dutyset, it would be easier to just be able to state that a duty has these tasks in these time slots and the duty occurs on these days. Hope I've made sense, kind regards, Matt |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
countif / sumproduct or something else?
Right, first of all my apologies for being so thick. Can I assume that Duty
1 say, which you give on Monday as being: No. 06:00 06:10 06:20 06:30 06:40 06:50 1 PA BM BM BM BM BM would be exactly the same if performed on Tuesday or Wednesday? If so then you could have a table of all the duties and use it as a lookup table then use a VLOOKUP() formula to return the Task. In other words: If I use the the data the you supplied for Monday as a table in Sheet3 A1 to G9 : No. 06:00 06:10 06:20 06:30 06:40 06:50 1 PA BM BM BM BM BM 2 BM BM BM BM BM BM 3 MHE MHE MHE MHE MHE MHE 4 MHE MHE MHE MHE MHE MHE 5 PM PM PM PM PM PM 6 PM PM PM PM PM PM 7 MHE MHE MHE MHE MHE MHE 8 MHE MHE MHE MHE MHE MHE then in the Summary sheet with headers in A1:H1: Duty Monday, Tuesday etc. and then in J1:N1 06:00 06:10 06:20 06:30 06:40 06:50 Finally enter the following formula in I2: =IF(COUNTA($A2:$D2)1,VLOOKUP($A2,Sheet3!$A$2:$G$9 ,COLUMN()-COLUMN($G$1),FALSE),"") and copy using the fill handle across to N2 and then down to N9 Now if you enter a Duty number in Column A and Y in any day Column then the tasks for that duty will display in the appropriate cells. If the Duties are not exactly the same regardless of what day they are performed how can you display the same Duty number doing different Tasks on different days? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "MJKelly" wrote in message ... Hi Sandy, I have 200 duties, one on each row. Each duty is broken down into ten minutes time slots so I know the task they are performing in each ten minutes (as the tasks change during the duty). Their are 30 different tasks which can be performed. So, originally each day of the week had a worksheet and each duty which occurred on that day was included. A simple Countif was used to count all instances of each task in the first time slot for the 200 duties, then the next summary cell counted the second time slot (again instances of the different tasks). I used a summary table for each day of the week. But, now I want to use one worksheet to contain the duties. Each row still represents one duty, but if the duty occurs on Monday or Tuesday etc then a "Y" is entered against that duty for the corresponding day column. How would I now produce a summary which checks to see if the duty occurs on a given day, and how many times each task occurs in the time slot for the total 200 duties. Again I would have a summary table for each day and a total table to show total amount for each task for the week (again in time slots). Like I say it works if the duties are input into different worksheets (one for each day), but for maintaining the dutyset, it would be easier to just be able to state that a duty has these tasks in these time slots and the duty occurs on these days. Hope I've made sense, kind regards, Matt |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
countif / sumproduct or something else?
Sandy sorted this out with sumproduct. I'll also try the possible solution above too (again form Sandy), to see if it works or to see how I might use this method in the future. many thanks, Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif and Sumproduct | Excel Worksheet Functions | |||
Sumproduct and Countif together | Excel Discussion (Misc queries) | |||
COUNTIF or SUMPRODUCT | Excel Discussion (Misc queries) | |||
SUMPRODUCT/COUNTIF | Excel Discussion (Misc queries) | |||
Countif or Sumproduct | Excel Discussion (Misc queries) |