Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function??
I would like a formula which will allow me to calculate the number of mondays
in a month which are either C- closed, H- holiday. or TT- term from the following list. This will be duplcated for each month. I have tried to use: =COUNTIF(B$2:B$32,"Monday")+COUNTIF(C$2:C$32,"H") but this adds the day and the letter. Can anyone help. Regards Kevan Date April '10 1 Thursday H 2 Friday C 3 4 5 Monday C 6 Tuesday H 7 Wednesday H 8 Thursday H 9 Friday H 10 11 12 Monday H 13 Tuesday TT 14 Wednesday TT 15 Thursday TT 16 Friday TT 17 18 19 Monday TT 20 Tuesday TT 21 Wednesday TT 22 Thursday TT 23 Friday TT 24 25 26 Monday TT 27 Tuesday TT 28 Wednesday TT 29 Thursday TT 30 Friday TT 31 Days 22 Term Time TT Monday 18 Tuesday 18 Wednesday 18 Thursday 19 Friday 19 Holiday H Monday 10 Tuesday 4 Wednesday 10 Thursday 11 Friday 11 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function??
Try
=SUMPRODUCT((A2:A200="Monday")*(B2:B200={"C","H"," TT"})) -- HTH Bob "Kevan" wrote in message ... I would like a formula which will allow me to calculate the number of mondays in a month which are either C- closed, H- holiday. or TT- term from the following list. This will be duplcated for each month. I have tried to use: =COUNTIF(B$2:B$32,"Monday")+COUNTIF(C$2:C$32,"H") but this adds the day and the letter. Can anyone help. Regards Kevan Date April '10 1 Thursday H 2 Friday C 3 4 5 Monday C 6 Tuesday H 7 Wednesday H 8 Thursday H 9 Friday H 10 11 12 Monday H 13 Tuesday TT 14 Wednesday TT 15 Thursday TT 16 Friday TT 17 18 19 Monday TT 20 Tuesday TT 21 Wednesday TT 22 Thursday TT 23 Friday TT 24 25 26 Monday TT 27 Tuesday TT 28 Wednesday TT 29 Thursday TT 30 Friday TT 31 Days 22 Term Time TT Monday 18 Tuesday 18 Wednesday 18 Thursday 19 Friday 19 Holiday H Monday 10 Tuesday 4 Wednesday 10 Thursday 11 Friday 11 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function??
Or to use your ranges
=SUMPRODUCT((B$2:B$32="Monday")*(C$2:C$32={"C","H" ,"TT"})) -- HTH Bob "Kevan" wrote in message ... I would like a formula which will allow me to calculate the number of mondays in a month which are either C- closed, H- holiday. or TT- term from the following list. This will be duplcated for each month. I have tried to use: =COUNTIF(B$2:B$32,"Monday")+COUNTIF(C$2:C$32,"H") but this adds the day and the letter. Can anyone help. Regards Kevan Date April '10 1 Thursday H 2 Friday C 3 4 5 Monday C 6 Tuesday H 7 Wednesday H 8 Thursday H 9 Friday H 10 11 12 Monday H 13 Tuesday TT 14 Wednesday TT 15 Thursday TT 16 Friday TT 17 18 19 Monday TT 20 Tuesday TT 21 Wednesday TT 22 Thursday TT 23 Friday TT 24 25 26 Monday TT 27 Tuesday TT 28 Wednesday TT 29 Thursday TT 30 Friday TT 31 Days 22 Term Time TT Monday 18 Tuesday 18 Wednesday 18 Thursday 19 Friday 19 Holiday H Monday 10 Tuesday 4 Wednesday 10 Thursday 11 Friday 11 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Function??
One way...
=SUMPRODUCT(--(B2:B32="Monday"),--(ISNUMBER(MATCH(C2:C32,{"C","H","TT"},0)))) -- Biff Microsoft Excel MVP "Kevan" wrote in message ... I would like a formula which will allow me to calculate the number of mondays in a month which are either C- closed, H- holiday. or TT- term from the following list. This will be duplcated for each month. I have tried to use: =COUNTIF(B$2:B$32,"Monday")+COUNTIF(C$2:C$32,"H") but this adds the day and the letter. Can anyone help. Regards Kevan Date April '10 1 Thursday H 2 Friday C 3 4 5 Monday C 6 Tuesday H 7 Wednesday H 8 Thursday H 9 Friday H 10 11 12 Monday H 13 Tuesday TT 14 Wednesday TT 15 Thursday TT 16 Friday TT 17 18 19 Monday TT 20 Tuesday TT 21 Wednesday TT 22 Thursday TT 23 Friday TT 24 25 26 Monday TT 27 Tuesday TT 28 Wednesday TT 29 Thursday TT 30 Friday TT 31 Days 22 Term Time TT Monday 18 Tuesday 18 Wednesday 18 Thursday 19 Friday 19 Holiday H Monday 10 Tuesday 4 Wednesday 10 Thursday 11 Friday 11 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
please help how to combine IF function with Countif function | Excel Worksheet Functions | |||
countif function? | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
CountIf Function Help | Excel Discussion (Misc queries) |