Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif based on two criteria
Hi, I have a number of duties which I have broken down by tasks. I want to summarise the amount of different tasks for each time window for each day. I have managed to do this with the following. =COUNTIF(Monday!H$4:H$1003,'All Shifts'!$B3) However, I have now moved all duties into a single worksheet (instead of 7 seperate worksheets - one for each day). I have added columns to state whether the duty occurs on a given day by putting "Y" in the necessary column. How do i use the above formula, but adapt it to check the status of the day of week column? I would use a table for day of the week, so each formula would be adjusted to check the appropriate day of week column. Hope you can help, Matt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif based on two criteria
Hi,
It's probably going to be sumproduct but without seeing your data layout then it's impossible to be precise. Does this get you mocing in the right direction? =SUMPRODUCT((H1:H30="xxx")*(J1:J30="Y")) Post back with some sample data Mike "MJKelly" wrote: Hi, I have a number of duties which I have broken down by tasks. I want to summarise the amount of different tasks for each time window for each day. I have managed to do this with the following. =COUNTIF(Monday!H$4:H$1003,'All Shifts'!$B3) However, I have now moved all duties into a single worksheet (instead of 7 seperate worksheets - one for each day). I have added columns to state whether the duty occurs on a given day by putting "Y" in the necessary column. How do i use the above formula, but adapt it to check the status of the day of week column? I would use a table for day of the week, so each formula would be adjusted to check the appropriate day of week column. Hope you can help, Matt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif based on two criteria
Thanks, but it's not having the desired effect.
Mon Tue Wed 06:00 06:10 06:20 Y Y Y PM PM PM Y Y PM XD PM The above should result in a table displaying monday data as follows 06:00 06:10 06:20 PM 2 1 2 XD 0 1 0 Hope this sheds some light? Matt |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif based on two criteria
Hi,
I built your table in A1 - F3 A B C D E F Mon Tue Wed 06:00 06:10 06:20 y y y PM PM PM Y Y PM XD PM and got the reuslt below with this formula =SUMPRODUCT(($A2:$A8="y")*(D2:D8="PM")) =SUMPRODUCT(($A2:$A8="y")*(D2:D8="XD")) The 2 formula go in the cells marked F and drag right 06:00 06:10 06:20 PM F 1 2 XD F 1 0 Mike "MJKelly" wrote: Thanks, but it's not having the desired effect. Mon Tue Wed 06:00 06:10 06:20 Y Y Y PM PM PM Y Y PM XD PM The above should result in a table displaying monday data as follows 06:00 06:10 06:20 PM 2 1 2 XD 0 1 0 Hope this sheds some light? Matt |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif based on two criteria
On Oct 13, 2:14*pm, Mike H wrote:
Hi, I built your table in A1 - F3 A * * * * * * * B * * * * * * * C * * * * * * * *D * * * * * E * * * * * * * *F Mon * * Tue * * Wed * * 06:00 * 06:10 * 06:20 y * * * y * * * y * * * PM * * *PM * * *PM Y * * * * * * * Y * * * PM * * *XD * * *PM and got the reuslt below with this formula =SUMPRODUCT(($A2:$A8="y")*(D2:D8="PM")) =SUMPRODUCT(($A2:$A8="y")*(D2:D8="XD")) The 2 formula go in the cells marked F and drag right * * * * 06:00 * 06:10 * 06:20 PM * * *F * * * 1 * * * 2 XD * * *F * * * 1 * * * 0 Mike "MJKelly" wrote: Thanks, but it's not having the desired effect. Mon Tue Wed 06:00 06:10 06:20 Y * * *Y * Y * * PM * * PM * PM Y * * * * * *Y * * PM * *XD * *PM The above should result in a table displaying monday data as follows * * * * 06:00 06:10 06:20 PM * 2 * * * *1 * * *2 XD * *0 * * * *1 * * *0 Hope this sheds some light? Matt- Hide quoted text - - Show quoted text - Works a treat, thanks very much. Kind regards, Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using COUNTIF to count based on multiple cell criteria. | Excel Worksheet Functions | |||
How do you create COUNTIF functions based on multiple criteria? | Excel Worksheet Functions | |||
An array and countif based on criteria in each column | Excel Worksheet Functions | |||
Countif based on 2 criteria | Excel Worksheet Functions | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions |