I am designing a sheet that shows a rota that will look like this

Name week 1 week 2 week 3
me 1 3 1
joe 1 2 4
dave 1 3 5

The numbers in the week colum will represent an area.
I would like a formula that will count how many numbers there are e.g how
many 1's in week 1 so i can see automatically that i have enough people in
that area. Eg if area 1 needs 3 people it will show up straight away if i
haven't got enough people roatad in that area. I can do condtionall
formatting on the end result but i cannot find somehting to give me the
result.


Cheers

Try the COUNTIF function.

At the bottom of your column, type "=COUNTIF(A1:A4,1)". That will tally up
the number of "1"s that appear in that column. Then copy the formula across.

Good luck!

"James" wrote:

I am designing a sheet that shows a rota that will look like this

Name week 1 week 2 week 3
me 1 3 1
joe 1 2 4
dave 1 3 5

The numbers in the week colum will represent an area.
I would like a formula that will count how many numbers there are e.g how
many 1's in week 1 so i can see automatically that i have enough people in
that area. Eg if area 1 needs 3 people it will show up straight away if i
haven't got enough people roatad in that area. I can do condtionall
formatting on the end result but i cannot find somehting to give me the
result.


Cheers

If week1 is in column B, then try this:

=COUNTIF(B2:B20,"1")

will count all the 1's in week1. Adjust the ranges and the number to
look at other weeks or other areas.

Hope this helps.

Pete

On May 6, 9:52*pm, James wrote:
I am designing a sheet that shows a rota that will look like this

Name week 1 week 2 week 3
me * * * * 1 * * * * *3 * * * * * *1
joe * * * * 1 * * * * *2 * * * * * *4
dave * * *1 * * * * *3 * * * * * *5

The numbers in the week colum will represent an area.
I would like a formula that will count how many numbers there are e.g how
many 1's in week 1 so i can see automatically that i have enough people in
that area. *Eg if area 1 needs 3 people it will show up straight away if i
haven't got enough people roatad in that area. *I can do condtionall
formatting on the end result but i cannot find somehting to give me the
result.

Cheers