Previous Topic Next Topic

Conditional Formating

Closed
rduncanhoops Posts 2 Registration date Thursday April 2, 2015 Status Member Last seen April 2, 2015 - Apr 2, 2015 at 03:59 PM
 RayH - Apr 3, 2015 at 01:29 PM
I have a spread sheet with 2 columns; In column 1(one) I am tracking Employees. in column 2 (two), I track the employees position. When a position is Vacant, there is no name in column 1(one) - it just states VACANT.
I want to apply a formula that will automatically look at a selection and tell me the number of total VACANT positions I have as well as the total positions and will automatically update my total whenever I fill a spot or delete an employee name and make it VACANT.
Thanks for any help.

2 responses

Answer 1 / 2
RayH
Apr 2, 2015 at 04:42 PM
Conditional formatting is not required to do this.

To find the total count:
=COUNTA(A1:A6)
Where range A1:A6 contains the set you want to count.

To find the 'VACANT' total:
=COUNTIF(B1:B6,"=VACANT")
Where range B1:B6 contains the 'VACANT' reference.

The values will automatically update when changed.
Answer 2 / 2
rduncanhoops Posts 2 Registration date Thursday April 2, 2015 Status Member Last seen April 2, 2015
Apr 2, 2015 at 05:02 PM
That worked, but I just realized I forgot some other data.
I have yet another column that tells the number of shifts. Anything < than 1.0 is considered part time. So now I want to know how many of my vacancies are part time and full time.
RayH
Apr 3, 2015 at 01:29 PM
Part time:
=COUNTIF(C1:C6,"<1")

Full time:
=COUNTIF(C1:C6,">=1")

Similar discussions

Highlight cell if another cell contains specific text
Abbi -
vcoolio -

Hello,

I have random text in cell C6 that I would like to highlight green if cell N6 has 'yes', red if cell N6 has 'no', and grey out if cell N6 has 'n/a'. Any suggestions for which formula to use?

Any help would be greatly appreciated!

1 response
How to use conditional format based on text?
Leanne99 -
Mooseman -

Hello,

I want a cell to change color if another cell contains the words "first aid". The text comes down from a drop down list and could read "First Aid" or "sleepover/first aid".

If the cell has "first aid" in it, I want the other cell to change color. I can get it to work if using formula f13=first aid but it won't change color if the cell says sleepover/first aid?

Can anyone help?

26 responses
Conditional formating - apply on more cells
Biljana -
Raj -

Hello,
i'm not so familiar with conditional formatting, so I would like to ask something:
I'm making a doc where Column A has number values, column B also numbers, of rank positions. Conditional formating of cell in column B would be: if number is greater than number in cell A, colour it Red, and if it is less than number in cell A, colour it green (rank position dropped down). Now, I managed that, but since the document has lots of rows, how do I apply the formating on the other cells in the column? If I make copy/paste to them, formatting doesn't apply on the whole column since it is not a function, but a cell format. And do I have to do the formating every day for every next...

32 responses
Conditional Formatting: 3 range of numbers
LCC -
LCC -

Hello,

would like to seek advice from some of the experts here.

I want to do conditional formatting from column O6-O21.

If percentage is above 100%, cell will be green filled with black font

If percentage between 80-100%, cell will be yellow filled with black font

If percentage is below 80%, cell will be red filled with black font

anyone can help?

THANK YOU!

Configuration: Windows XP / Chrome 42.0.2311.90

2 responses
Conditional formating of row based on other cells
Bitbull -
TrowaD -

Hello,
I am trying to format cells A7-E7 based on the relationship between C6 and C5.
Ideally they would be coloured on a scale between green and yellow if C6 was between C5 and 0 and would be red if C6>C5.
Thanks,

Configuration: Windows 7 / Firefox 31.0

1 response