Countif

[bill351]

Hi Guys

I am trying to equate the working hours in a roster to check for budget anomalies and in a row for the fortnight I have the days worked (0730-1930) and the rostered days off (R/O) now the formula I am trying to get to work is =COUNTIF(D79:R79,"*")-COUNTIF(D79:R79,"R/O") and from this I get the number of days on which is fine but I would like that to calculate to hours by multiplying the number of days on by 12 but I dont know where to add it in to the above formula. The only constants are the R/O and that days are 12hrs but the times are different. I could remove the R/O and just have =COUNTIF(D79:R79,"*")*12 and that gives the hours but changes the appearance of the roster which I am trying to avoid. Any Idea??

Cheer Bill from Oz

 

[BrianJN1]

Hi Guys

I am trying to equate the working hours in a roster to check for budget anomalies and in a row for the fortnight I have the days worked (0730-1930) and the rostered days off (R/O) now the formula I am trying to get to work is =COUNTIF(D79:R79,"*")-COUNTIF(D79:R79,"R/O") and from this I get the number of days on which is fine but I would like that to calculate to hours by multiplying the number of days on by 12 but I dont know where to add it in to the above formula. The only constants are the R/O and that days are 12hrs but the times are different. I could remove the R/O and just have =COUNTIF(D79:R79,"*")*12 and that gives the hours but changes the appearance of the roster which I am trying to avoid. Any Idea??

Cheer Bill from Oz

Hi Bill.
You have me a little confused.
It mainly surrounds this formula: =COUNTIF(D79:R79,"*")-COUNTIF(D79:R79,"R/O")
What is the significance of the "*"?

It seems to me with my playing around that "*" is not liked. It would seem that something alphanumeric would be a better choice. Then you can probably move on. Please share your thoughts.

 

[bill351]

Hi BrianJN1

The significance of the "*" is because the input to the cells for the shift times are varied so a specific Countif cant be done hence the use of the wildcard. So with that formula I get the count of all the cells that dont have R/O which give me the number but not the hours and because others use this sheet I cant make to many visual changes from the current format.

 

[BrianJN1]

Apologies Bill. Last night when I was working this I had actually arrived at a means that might satisfy you but I doubted it as the formula =countif(D79:R79,"*") counted all "*" as well as "R/O" in the row; I didn't understand the "wildcard". Try this formula: =(COUNTIF(D79:R79,"*")-COUNTIF(D79:R79,"r/o"))*12 I think this is what you seek.

 

[bill351]

thankyou I will give that a try tomorrow when I get to work