Is it a Countif I need

[North for Short]

 

[hagia_sofia]

I am not sure whether I understand it correctly but if you would like to count non-blank cells, then you can use COUNTA function which returns the count of cells that contain numbers, text, logical values, error values, but does not count empty cells.

 

[North for Short]

Hi Hagia. I need to include both. For instance. Spiderman is on super hero duty on duty 2 but Green Goblin is also covering superhero duty 2 on tuesday. It tells me then that the actual duty is covered all week. Where the duty isn't covered will come up red as in on the Saturday with Carnage. (Obviously these are ficticious names but you can see that I am covering a shift rota of some sorts).

 

[hagia_sofia]

So, are you interested only in cases when there is nobody in a cell? Or something else? If the former is true, i.e. if the result is supposed to be 6 - 6 - 6 - 5, then use COUNTA where your range are individual rows...

 

[North for Short]

So, are you interested only in cases when there is nobody in a cell? Or something else? If the former is true, i.e. if the result is supposed to be 6 - 6 - 6 - 5, then use COUNTA where your range are individual rows...

Not sure what you mean. There is text not numbers in the cells

 

[hagia_sofia]

COUNTA will count the number of non-blank cells, i.e. provided that MON = column D and SAT = column I, then COUNTA(D4:I4) will return weekly count equal to 6 while COUNTA(D7:I7) will return 5 because there is one blank cell on SAT.

 

[North for Short]

COUNTA will count the number of non-blank cells, i.e. provided that MON = column D and SAT = column I, then COUNTA(D4:I4) will return weekly count equal to 6 while COUNTA(D7:I7) will return 5 because there is one blank cell on SAT.

Nope that just gave me a total of cells that contained text. My formula at the moment is =countif(D4:d7,c4) This will give me a blank cell, which means that duty is covered. Where is no coverage, there will be a read block (contains no text) however in the instance of Green Goblin covering the duty, I want to see a blank cell to notify that the duty is covered. Does that help?

 

[hagia_sofia]

I am still not sure whether I follow. Are you interested in counting something or in conditional formatting the table on the ring, i.e. in marking red when there is a blank cell?

 

[North for Short]

I am still not sure whether I follow. Are you interested in counting something or in conditional formatting the table on the ring, i.e. in marking red when there is a blank cell?

Thank you for looking into it however things have changed and this is no longer required. I do appreciate the help but seeing that I have less than a year before retirement, if the bosses want a bog standard excel sheet that dispalys pretty colours, they can have that.

 

[North for Short]

I am revive this query as I want to do it for myself. Here I have a duty roster for a ficticious parcel company and on the left hand side you the duty number, duty name amd the duty holder. On certain days, the duty holder is having a day off, so a reserve is used (vicky snow). On certain days you can see that Paddy Murphy is not in on Monday and Andrew McDougal is off on Saturday.
On the right hand side is a spread sheet with the criteria that counts the number of John Smith in a Column (there is a conditional format being used when a duty is not covered but Vicky is covering John Smith on Tuesday so it has been included with the count (weekly count is 5, I want 6 across the week). I am not sure if countif (s) is the right function or should an if function be included. I am asking can it first of all be done. So when I insert a name for instance Paddy Murphy on Monday is covered by Vicky on overtime. It will still show 4 but the duty is now covered. How can I show using a function to tell me that the duty is covered.