If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|